select t.username,t.datetime,count(1) as total from t_info t, (select to_date('2009-08-01','yyyy-mm-dd')+rownum-1 datetime from dual connect by level <=to_date('2009-08-31','yyyy-mm-dd') - to_date('2009-08-01','yyyy-mm-dd')+1) b where t.datetime(+) = b.datetime group by t.username,t.datetime order by username,datetime;试试吧,没测试
IID USERNAME DATETIME RECORD 1 aa 2009-08-01 a1 2 bb 2009-08-01 b1 3 cc 2009-08-03 c1 4 dd 2009-08-06 d1 5 aa 2009-08-02 a2SELECT user_name, stat_date, COUNT(record) AS total FROM (select user_name,stat_date from (select column_value user_name from table(sys.odcivarchar2list('aa','bb','cc','dd','ee'))) a, (select beg_date + level - 1 as stat_date from (select to_date('2009-08-01','yyyy-mm-dd') beg_date ,to_date('2009-08-31','yyyy-mm-dd') end_date from dual) connect by level <= (end_date - beg_date)+1) order by 1,2) a, t_info b WHERE a.user_name = b.username(+) and a.stat_date = to_date(b.datetime(+),'yyyy-mm-dd') GROUP BY user_name, stat_date ORDER BY user_name, stat_date ASC;USER_NAME STAT_DATE TOTAL aa 2009-8-1 1 aa 2009-8-2 1 aa 2009-8-3 0 aa 2009-8-4 0 aa 2009-8-5 0 aa 2009-8-6 0 aa 2009-8-7 0 aa 2009-8-8 0 aa 2009-8-9 0 aa 2009-8-10 0 aa 2009-8-11 0 aa 2009-8-12 0 aa 2009-8-13 0 aa 2009-8-14 0 aa 2009-8-15 0 aa 2009-8-16 0 aa 2009-8-17 0 aa 2009-8-18 0 aa 2009-8-19 0 aa 2009-8-20 0 aa 2009-8-21 0 aa 2009-8-22 0 aa 2009-8-23 0 aa 2009-8-24 0 aa 2009-8-25 0 aa 2009-8-26 0 aa 2009-8-27 0 aa 2009-8-28 0 aa 2009-8-29 0 aa 2009-8-30 0 aa 2009-8-31 0 bb 2009-8-1 1 bb 2009-8-2 0 bb 2009-8-3 0 bb 2009-8-4 0 bb 2009-8-5 0 bb 2009-8-6 0 bb 2009-8-7 0 bb 2009-8-8 0 bb 2009-8-9 0 bb 2009-8-10 0 bb 2009-8-11 0 bb 2009-8-12 0 bb 2009-8-13 0 bb 2009-8-14 0 bb 2009-8-15 0 bb 2009-8-16 0 bb 2009-8-17 0 bb 2009-8-18 0 bb 2009-8-19 0 bb 2009-8-20 0 bb 2009-8-21 0 bb 2009-8-22 0 bb 2009-8-23 0 bb 2009-8-24 0 bb 2009-8-25 0 bb 2009-8-26 0 bb 2009-8-27 0 bb 2009-8-28 0 bb 2009-8-29 0 bb 2009-8-30 0 bb 2009-8-31 0 cc 2009-8-1 0 。
select a.dt,td.cust_type,count(td.order_num) from t_info td,(select date '2009-08-01' +rownum-1 dt from dual connect by rownum <= trunc(last_day(date '2009-08-01')-date '2009-08-01')+1 ) a where a.dt = trunc(td.order_date(+)) group by a.dt,td.cust_type order by a.dt,td.cust_type;
支持1楼 不过得把count(1)改成count(t.record)
当然有区别了,count(1)返回的结果至少会有1 明显不符合楼主的要求。不过看来换成count(1)也不行,左连接的那个数据集得改下 select b.username,b.datetime,count(t.record) as total from t_info t right join (select * from(select to_date('2009-08-01','yyyy-mm-dd')+rownum-1 datetime from dual connect by level <=to_date('2009-08-31','yyyy-mm-dd') - to_date('2009-08-01','yyyy-mm-dd')+1), (select distinct username from t_info)) b on to_date(t.datetime,'YYYYMMDD') = b.datetime and b.username=t.username group by b.username,b.datetime order by username,datetime;
from t_info t,
(select to_date('2009-08-01','yyyy-mm-dd')+rownum-1 datetime from dual
connect by level <=to_date('2009-08-31','yyyy-mm-dd') - to_date('2009-08-01','yyyy-mm-dd')+1) b
where t.datetime(+) = b.datetime
group by t.username,t.datetime
order by username,datetime;试试吧,没测试
B表中的转一下
1 aa 2009-08-01 a1
2 bb 2009-08-01 b1
3 cc 2009-08-03 c1
4 dd 2009-08-06 d1
5 aa 2009-08-02 a2SELECT user_name, stat_date, COUNT(record) AS total
FROM (select user_name,stat_date
from (select column_value user_name from table(sys.odcivarchar2list('aa','bb','cc','dd','ee'))) a,
(select beg_date + level - 1 as stat_date
from (select to_date('2009-08-01','yyyy-mm-dd') beg_date
,to_date('2009-08-31','yyyy-mm-dd') end_date
from dual)
connect by level <= (end_date - beg_date)+1)
order by 1,2) a,
t_info b
WHERE a.user_name = b.username(+)
and a.stat_date = to_date(b.datetime(+),'yyyy-mm-dd')
GROUP BY user_name, stat_date
ORDER BY user_name, stat_date ASC;USER_NAME STAT_DATE TOTAL
aa 2009-8-1 1
aa 2009-8-2 1
aa 2009-8-3 0
aa 2009-8-4 0
aa 2009-8-5 0
aa 2009-8-6 0
aa 2009-8-7 0
aa 2009-8-8 0
aa 2009-8-9 0
aa 2009-8-10 0
aa 2009-8-11 0
aa 2009-8-12 0
aa 2009-8-13 0
aa 2009-8-14 0
aa 2009-8-15 0
aa 2009-8-16 0
aa 2009-8-17 0
aa 2009-8-18 0
aa 2009-8-19 0
aa 2009-8-20 0
aa 2009-8-21 0
aa 2009-8-22 0
aa 2009-8-23 0
aa 2009-8-24 0
aa 2009-8-25 0
aa 2009-8-26 0
aa 2009-8-27 0
aa 2009-8-28 0
aa 2009-8-29 0
aa 2009-8-30 0
aa 2009-8-31 0
bb 2009-8-1 1
bb 2009-8-2 0
bb 2009-8-3 0
bb 2009-8-4 0
bb 2009-8-5 0
bb 2009-8-6 0
bb 2009-8-7 0
bb 2009-8-8 0
bb 2009-8-9 0
bb 2009-8-10 0
bb 2009-8-11 0
bb 2009-8-12 0
bb 2009-8-13 0
bb 2009-8-14 0
bb 2009-8-15 0
bb 2009-8-16 0
bb 2009-8-17 0
bb 2009-8-18 0
bb 2009-8-19 0
bb 2009-8-20 0
bb 2009-8-21 0
bb 2009-8-22 0
bb 2009-8-23 0
bb 2009-8-24 0
bb 2009-8-25 0
bb 2009-8-26 0
bb 2009-8-27 0
bb 2009-8-28 0
bb 2009-8-29 0
bb 2009-8-30 0
bb 2009-8-31 0
cc 2009-8-1 0
。
from t_info td,(select date '2009-08-01' +rownum-1 dt
from dual
connect by rownum <= trunc(last_day(date '2009-08-01')-date '2009-08-01')+1
) a
where a.dt = trunc(td.order_date(+))
group by a.dt,td.cust_type
order by a.dt,td.cust_type;
不过得把count(1)改成count(t.record)
明显不符合楼主的要求。不过看来换成count(1)也不行,左连接的那个数据集得改下
select b.username,b.datetime,count(t.record) as total
from t_info t right join
(select * from(select to_date('2009-08-01','yyyy-mm-dd')+rownum-1 datetime from dual
connect by level <=to_date('2009-08-31','yyyy-mm-dd') - to_date('2009-08-01','yyyy-mm-dd')+1),
(select distinct username from t_info)) b
on to_date(t.datetime,'YYYYMMDD') = b.datetime
and b.username=t.username
group by b.username,b.datetime
order by username,datetime;