select DATE_FORMAT(p1.time,'%Y-%m-%d') grouptime,COUNT(1) upcount,p2.count
from table p1 left join (SELECT DATE_FORMAT(time,'%Y-%m-%d') day,COUNT(1) count FROM table where flowtype=1 GROUP BY day) p2
on DATE_FORMAT(p1.time,'%Y-%m-%d')=p2.`day`
where p1.flowtype=2
GROUP BY grouptime
在你的思路上left join一下即可。
是这样的,我现在count每天的数据,每天的都统计,如果这一天没有数据,给我显示0,而不是直接跳过不显示这一天,现在就是好比如,2018-06-11这天,没有flowtype=2的数据,我count的时候他就直接不显示这天,导致我后面的一下关联没法再时间上关联
附上我想的sql
select DATE_FORMAT(p1.time,'%Y-%m-%d') grouptime,COUNT(1) upcount , p2.count
from table p1 ,(SELECT DATE_FORMAT(time,'%Y-%m-%d') day, COUNT(1) count FROM table where flowtype=1 GROUP BY day) p2
where p1.flowtype=2 AND DATE_FORMAT(time,'%Y-%m-%d')=p2.`day`
GROUP BY grouptime
[/quote]
那你只能搞个表,只有一个日期列,保存每天的日期,然后左关联,不然没法玩
是这样的,我现在count每天的数据,每天的都统计,如果这一天没有数据,给我显示0,而不是直接跳过不显示这一天,现在就是好比如,2018-06-11这天,没有flowtype=2的数据,我count的时候他就直接不显示这天,导致我后面的一下关联没法再时间上关联
附上我想的sql
select DATE_FORMAT(p1.time,'%Y-%m-%d') grouptime,COUNT(1) upcount , p2.count
from table p1 ,(SELECT DATE_FORMAT(time,'%Y-%m-%d') day, COUNT(1) count FROM table where flowtype=1 GROUP BY day) p2
where p1.flowtype=2 AND DATE_FORMAT(time,'%Y-%m-%d')=p2.`day`
GROUP BY grouptime
oracle下可以用如下查询,其他的数据库按照自己需求改!
select a.ymd,decode(b.flowtype,null,0,b.flowtype) as flowtype
from
(
select to_char(time,'yyyy/mm/dd') as ymd
from your_table
group by to_char(time,'yyyy/mm/dd')
) a
left outer join
(
select
flowtype,count(flowtype) count,to_char(time,'yyyy/mm/dd') ymd
from your_table
where flowtype in ('1','2')
group by to_char(time,'yyyy/mm/dd'),flowtype
order by to_char(time,'yyyy/mm/dd'), flowtype
) b
on a.ymd = b.ymd