mysql 相想查询七天之前的总数,查询 6 天之前的总数 5 天之前的总数。。。1 天之前的总数 当天总数 并按照七天日期分组,
有没有一条语句就能解决的办法呢 目前是
SELECT COUNT(id) as count FROM applist
WHERE time<DATE_SUB(NOW(), INTERVAL 6 DAY)
循环执行进行查询
有没有更好的办法呢
1
liprais 2020-02-03 17:52:22 +08:00
算出每天的量,再去选不就行了
|
2
phpfpm 2020-02-03 17:55:07 +08:00
你直接 group by 一下然后用程序 sum 得了。
另一个思路:group by 然后自连接(想想 howto |
3
zzn 2020-02-03 18:05:29 +08:00
可以生写多个 count filter, `COUNT(*) FILTER (WHERE <condition>)`
类似这样: ``` SELECT COUNT(id) FILTER( WHERE time<DATE_SUB(NOW(), INTERVAL 6 DAY)) AS _6, COUNT(id) FILTER( WHERE time<DATE_SUB(NOW(), INTERVAL 5 DAY)) AS _5 ``` |
4
akira 2020-02-03 18:05:50 +08:00
每天生成一次比较合理吧
|
6
toesbieya 2020-02-03 18:17:22 +08:00
和 3 楼写的差不多,
SELECT COUNT(0) count7,sum(if(time<DATE_SUB(NOW(),INTERVAL 6 DAY),1,0)) count6... FROM applist WHERE time<DATE_SUB(NOW(), INTERVAL 7 DAY) |
7
lxk11153 2020-02-03 19:34:56 +08:00
头像交出来
|
8
chaodada OP @lxk11153 #7 http://www.sohu.com/a/238654495_612229 去这里拿吧 原图我也没找到哈哈哈哈哈哈哈哈
|
9
chaodada OP |
10
chaodada OP @phpfpm #2
@zzn #3 @zzn #5 大佬们我最终写成了这样的到了我想要的结果 不知道有没有更好的办法 select temp.* from ( SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 6 DAY) as count_time FROM `test` WHERE `reg_time`<DATE_SUB(NOW(), INTERVAL 6 DAY) UNION ALL SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 5 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 5 DAY) UNION ALL SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 4 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 4 DAY) UNION ALL SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 3 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 3 DAY) UNION ALL SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 2 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 2 DAY) UNION ALL SELECT COUNT(`user_id`) as count ,DATE_SUB(NOW(), INTERVAL 1 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 1 DAY) UNION ALL SELECT COUNT(`user_id`) as count , DATE_SUB(NOW(), INTERVAL 0 DAY) as count_time FROM `test` WHERE `reg_time`< DATE_SUB(NOW(), INTERVAL 0 DAY) ) as temp |