我有一张这样的桌子-
CREATE TABLE sent_bulletin_list
(
`id` int,
`date` found_time,
`value` int,
);
而且，我希望每天都能找到最后7天的条目总数。
我所做的是-
SELECT
DATE(found_time) as date,
count( * ) as total
FROM
sent_bulletin_list
WHERE
found_time > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
GROUP BY
DATE(found_time);
然后找到这样的东西-
+------------+-------+
| date | total |
+------------+-------+
| 2016-07-01 | 8 |
+------------+-------+
但是我喜欢吃这样的东西-
+------------+-------+
| date | total |
+------------+-------+
| 2016-06-25 | 0 |
| 2016-06-26 | 0 |
| 2016-06-27 | 0 |
| 2016-06-28 | 0 |
| 2016-06-29 | 0 |
| 2016-06-30 | 0 |
| 2016-07-01 | 8 |
+------------+-------+
我试过case像这-
count( CASE found_time IS NOT NULL
THEN 1
ELSE 0
END AS date )
但这也行不通。
谁能帮忙吗？
在此先感谢您的帮助。
您需要一个表，其中date range查询中指定的所有日期都位于该表中。
您可以尝试一下：
SELECT
dateTable.day,
COALESCE(your_query.total,0) AS total
FROM
(
SELECT DATE(ADDDATE(FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )), 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(FROM_UNIXTIME(UNIX_TIMESTAMP()), FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )))
) dateTable
LEFT JOIN
(
SELECT
DATE(found_time) as date,
count( * ) as total
FROM
sent_bulletin_list
WHERE
found_time > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
GROUP BY DATE(found_time)
) your_query
ON dateTable.day = your_query.date
ORDER BY dateTable.day
测试：
假设您的表中包含以下数据：
DROP TABLE IF EXISTS `sent_bulletin_list`;
CREATE TABLE `sent_bulletin_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`found_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO `sent_bulletin_list` VALUES ('1', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('2', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('3', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('4', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('5', '2016-07-17 00:00:00');
然后运行上面的查询将为您提供以下输出：
输出：
day total
2016-07-14 0
2016-07-15 0
2016-07-16 0
2016-07-17 5
2016-07-18 0
2016-07-19 0
2016-07-20 0
2016-07-21 0
笔记：
您需要将end and start date范围放在这里：
@i < DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP()), FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )))
并且您需要将您放在start date这里：
SELECT DATE(ADDDATE(FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )), INTERVAL @i:=@i+1 DAY)) AS DAY
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句