询问一个跟SQL分组相关的问题

Question

本帖最后由 zliuzz 于 2009-08-29 09:55:04 编辑

Answer 1

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;试试吧，没测试

Answer 2

to_char(to_date('2009-08-01','yyyy-mm-dd')+rownum-1,'yyyy-mm-dd')
B表中的转一下

Answer 3

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
。

Answer 4

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;

Answer 5

支持1楼
不过得把count(1)改成count(t.record)

Answer 6

当然有区别了，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;