php - 单个查询以获取过去 7 天的个人计数

Question

我确实知道下面的条件会返回过去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
..
..
..

Answer 1

这将为您提供日期和计数。

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)