求教大佬数据库问题

[chaodada]

mysql 相想查询七天之前的总数，查询 6 天之前的总数 5 天之前的总数。。。1 天之前的总数 当天总数 并按照七天日期分组， 有没有一条语句就能解决的办法呢 目前是 SELECT COUNT(id) as count FROM applist WHERE time<DATE_SUB(NOW(), INTERVAL 6 DAY) 循环执行进行查询 有没有更好的办法呢

[liprais]

算出每天的量，再去选不就行了

[phpfpm]

你直接 group by 一下然后用程序 sum 得了。

另一个思路：group by 然后自连接（想想 howto

[zzn]

可以生写多个 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
```

[akira]

每天生成一次比较合理吧

[zzn]

@zzn 忘了说，pg 可以这样写，mysql 不知道行不行

[toesbieya]

和 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)

[lxk11153]

头像交出来

[chaodada]

@lxk11153 #7 http://www.sohu.com/a/238654495_612229 去这里拿吧 原图我也没找到哈哈哈哈哈哈哈哈

[chaodada]

@toesbieya #6
@zzn #5
@akira #4
@zzn #3
@phpfpm #2
@liprais #1 感谢大佬们的指教 我写一下实施

[chaodada]

@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

[phpfpm]

首先 你最好有一个维度表（各个日期的维度表）
select date from date_list
join xxx on xxx.date>{begin date}

这样将数据从 n 天 m 个数据扩展成 n*m 行

然后 group by date
然后 count 你的条件就好（某天单独的，大于某天的）等