单次查询可获得过去7天个人的计数 - Single query to get count of individual of past 7 days

Single query to get count of individual of past 7 days

我知道以下条件将返回过去7天内的总数

SELECT count(id) FROM registration 
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()

是否有一个单一的查询，我可以做得到过去7天返回在每个单独的一天的7个结果的数组?

例如:

day 1 - 10
day 2 - 5
day 3 - 9
..
..
..

显示日期和计数。

SELECT DATE(createdDate),COUNT(id)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)

或者，为了给出更接近示例的结果，您可以使用:

SELECT CONCAT("Day ",DATEDIFF(NOW(), createdDate)) AS day,COUNT(id)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)

添加group-by子句:

SELECT count(id), DATE(createdDate)
FROM registration
WHERE createdDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdDate)

select 8 - n day, count(id)
from registration
join (select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7) x
on createdDate between date_sub(curdate(), interval n day) and date_sub(curdate(), interval n-1 day)
group by day
order by day