在日期范围内的天数和总天数。

I have the following table:

我有以下表格:

id | start_date  |  end_date  | client_id
1    2013-08-01    2013-08-09       1
2    2013-08-10    2013-08-10       1
3    2013-08-10    2013-08-17       1
4    2013-08-18    2013-08-18       1
5    2013-08-18    2013-08-18       1
6    2013-08-18    2013-08-31       1
7    2013-08-01    2013-08-09       2
8    2013-08-11    2013-08-11       2
9    2013-08-11    2013-08-17       2
10   2013-08-19    2013-08-20       2

what I'm trying to do is count the number of days that each client was present without repeating the days for each client, so from the previous data I'm looking to get:

我要做的是计算每个客户出席的天数，而不是重复每个客户出席的天数，所以从前面的数据中，我希望得到:

client_id | total_days
    1           31
    2           18

So for client 1 I get 31 because he was "present" for 31 days, from 8/1/2013 - 8/31/2013 with no gaps, and for client 2 I get 18 because he was present for 18 days:

因此，对于客户1，我得到31，因为他在31天内“在场”，从2013年的8月1日到2013年8月31日，没有间断，而对于客户2，我得到了18天，因为他有18天的时间:

8/1 - 8/9 = 9 days 
8/11 - 8/17 = 7 days 
8/19 - 8/20 = 2 days

is there anyway to achieve this in MySQL, I've been trying for a while but have no idea on how to do it.

在MySQL中是否有实现这一点的方法，我已经尝试了一段时间，但是不知道怎么做。

This is the fiddle

这是小提琴

2 个解决方案

#1

If overlapping ranges exist, then I suggest building a driver table that is a list of dates, then JOIN to that table using BETWEEN:

如果存在重叠的范围，那么我建议构建一个驱动表，它是一个日期列表，然后使用以下方法连接到该表:

SELECT a.Client_ID, COUNT(DISTINCT b.Date)
FROM YourTable a
JOIN Dates b
  ON b.Date BETWEEN a.start_date  AND a.end_date
GROUP BY a.Client_ID

Demo: SQL Fiddle

演示:SQL小提琴

There are plenty of places to find calendar table logic, here's one.

有很多地方可以找到日历表逻辑，这里有一个。

If ranges never overlap then you can use SUM(DATEDIFF()).

如果范围从不重叠，那么可以使用SUM(DATEDIFF()))。

#2

If there are no overlapping ranges. You can use the following query:

如果没有重叠的范围。您可以使用以下查询:

SELECT Client_id,
       Sum(DATEDIFF(End_date, Start_date)) AS `Present`
FROM TABLE
GROUP BY Client_id;

This will give you an overview of the number of days a client was present.

这将使您大致了解客户出席的天数。

#1