SELECT CASE count(*) login_times --用decode也可以 WHEN 0 then 0 ELSE count(*), to_char(date,'yyyy-mm-dd') END FROM a GROUP BY to_char(date,'yyyy-mm-dd')
楼上方法的行吗,好像根本不存在2005-06-13这个分组,记录中怎么会有呢? 如果只要6-13号的:select count(*) login_times,to_char(date,'yyyy-mm-dd') from a group by to_char(date,'yyyy-mm-dd') union all select 0,'2005-06-13' from dual where not exists( select 1 from a where to_char(date,'yyyy-mm-dd') = '2005-06-13')
select t1.alldate,count(t2.date1) from (select (to_date('2005-06-01','yyyy mm dd') + rownum - 1) alldate from all_objects where rownum < 31) t1,(select to_char(date,'yyyy-mm-dd') date1 from a) t2 where t1.alldate = t2.date1(+) group by t1.alldate
记录
login_times(登录次数) date
0 2005-06-13
所以统计不出来的。
login_times(登录次数) date
0 2005-06-13
......
0 2005-06-13
1 2005-06-14
3 2005-06-15
还是只要6-13号的?
WHEN 0 then 0
ELSE count(*),
to_char(date,'yyyy-mm-dd')
END
FROM a
GROUP BY to_char(date,'yyyy-mm-dd')
如果只要6-13号的:select count(*) login_times,to_char(date,'yyyy-mm-dd') from a group by to_char(date,'yyyy-mm-dd')
union all
select 0,'2005-06-13' from dual where not exists( select 1 from a where to_char(date,'yyyy-mm-dd') = '2005-06-13')
(select (to_date('2005-06-01','yyyy mm dd') + rownum - 1) alldate from all_objects where rownum < 31) t1,(select to_char(date,'yyyy-mm-dd') date1 from a) t2
where t1.alldate = t2.date1(+)
group by t1.alldate