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

[zliuzz]

我有以下一张表
T_info
iid int primary_key,
username varchar2(20),
datetime varchar2(10),
record varchar2(50),

其中username存放的是用户名 datetime存放的是日期 格式yyyy-MM-dd record是这个人说过的一句话

现在我想统计 从2009-08-01 到 2009-08-30 每人每天说过几句话 不知道要怎么搞
我写了一个分组语句

select username,datetime,count(*) as total from t_info where 
datetime>='2009-08-01' and datetime < ='2009-08-31' 
group by username,datetime order by username,datetime asc;

但是如果有人有一天没有说话 那执行这个SQL语句的结果 将会没有这一天的数据
比如说有个人2009-08-02没有说话 那结果就变成
username,2009-08-01,count
username,2009-08-03,count
其中没有2009-08-02的数据
我想让他能有差的那一条数据 并把count值赋为0 就变成
username,2009-08-01,count
username,2009-08-02,0
username,2009-08-03,count

不知道要怎样写SQL语句

[阿三]

[Quote=引用 5 楼 wildwave 的回复:]
支持1楼
不过得把count(1)改成count(t.record)
[/Quote]
这个没有什么区别吧

[shiyiwan]

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;

试试吧，没测试

[shiyiwan]

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

[非萨范德萨发]

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	a2

SELECT 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
。。。。

[sxx_tt]

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;

[小灰狼W]

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

[小灰狼W]

[Quote=引用 6 楼 zxf_feng 的回复:]
引用 5 楼 wildwave 的回复:
支持1楼
不过得把count(1)改成count(t.record)

这个没有什么区别吧
[/Quote]
当然有区别了，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;