mysql - 获取给定范围内所有日期的结果，包括不存在的日期

Question

(更新)
我有如下两个表

表订阅者:

id | service_id | subscribe_date

1  | 1          | 2016-08-10
2  | 2          | 2016-08-09
3  | 2          | 2016-08-05
4  | 1          | 2016-08-03

表服务:

id | service_name

1  | test1          
2  | test2  
3  | test2 

subscribers.service_id 在 services.id 上有一个外键

我想从下面的表格中获取数据:

service_id | subscribe_date | count

1        | 2016-08-10       | 1  
1        | 2016-08-09       | 0
2        | 2016-08-10       | 0  
2        | 2016-08-09       | 1
3        | 2016-08-10       | 0  
3        | 2016-08-09       | 0

我试图通过以下查询获取此数据:

SELECT COUNT(*), subscribe_date, service_id
FROM subscribers
    INNER JOIN services ON subscribers.service_id = services.id
WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
GROUP BY service_id, subscribe_date;

但是我没有成功。我得到以下结果:

1        | 2016-08-10       | 1  
2        | 2016-08-09       | 1

Answer 1

给你:

由于您没有任何calendar 表，因此我们需要通过查询创建给定日期范围(含)之间的所有日期。但就像我说的，你需要同意 terms & conditions这个查询之前使用它。

SELECT 
dateWiseServices.id AS service_id,
dateWiseServices.`Day` AS subscribed_date,
COALESCE(yourQuery.total,0) AS cnt

FROM 
(
    SELECT 
    S.id,
    dateTable.Day
    FROM 
    (
    SELECT ADDDATE('2016-08-09', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-10', '2016-08-09')
    ) AS dateTable
    CROSS JOIN Services S
) AS dateWiseServices

LEFT JOIN 
(
    SELECT COUNT(*) AS total, subscribe_date, service_id
    FROM subscribers
            INNER JOIN services ON subscribers.service_id = services.id
    WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
    GROUP BY service_id, subscribe_date
) AS yourQuery 
ON dateWiseServices.id = yourQuery.service_id AND dateWiseServices.`Day` = yourQuery.subscribe_date
ORDER BY dateWiseServices.id, dateWiseServices.`Day` DESC