关于分组,一个很有意义的问题

[romail]

假如一个表tb1有以下数据:

id date num
a 2005-1-1 3
a 2005-1-1 2
c 2005-1-1 3
b 2005-1-2 4
b 2005-1-2 1
c 2005-1-2 1
a 2005-1-3 2
a 2005-1-3 4
c 2005-1-3 3
c 2005-1-3 2

我现在想查出date为2005-1-3的所有的id的num的当日合计数以及他们的累计数（即：包括他们2005-1-1、2005-1-2的num），该怎样用一条SQL语句实现？

结果应如下：
id 当日合计数 累计数
a 6 11
c 5 9

[pbsql]

select id,
当日合计数=sum(case when datediff(day,[date],'2005-1-3')=0 then num else 0 end),
累计数=sum(num)
where id in(select id from tbl where datediff(day,[date],'2005-1-3')=0)
group by id

[xiaomeixiang]

declare @tb1 table(id varchar(5), date datetime, num int)
insert into @tb1
select 'a','2005-1-1', 3
union all select 'a' , '2005-1-1' , 2
union all select 'c' , '2005-1-1' , 3
union all select 'b' , '2005-1-2' , 4
union all select 'b' , '2005-1-2' , 1
union all select 'c' , '2005-1-2' , 1
union all select 'a' , '2005-1-3' , 2
union all select 'a' , '2005-1-3' , 4
union all select 'c' , '2005-1-3' , 3
union all select 'c', '2005-1-3' , 2

select a.id,当日合计数,累计数 from
(select id,sum(num) as 当日合计数 from @tb1 where date='2005-1-3' group by id ) a left join
(select id,sum(num) as 累计数 from @tb1 where date<='2005-1-3' group by id ) b on a.id=b.id

[jinjazz]

--全部计算
--建立测试环境
Create Table 表(id varchar(10),date varchar(10),num integer)
--插入数据
insert into 表
select 'a','2005-1-1','3' union
select 'a','2005-1-1','2' union
select 'c','2005-1-1','3' union
select 'b','2005-1-2','4' union
select 'b','2005-1-2','1' union
select 'c','2005-1-2','1' union
select 'a','2005-1-3','2' union
select 'a','2005-1-3','4' union
select 'c','2005-1-3','3' union
select 'c','2005-1-3','2'
select * from 表
--测试语句
select id,date, 当日合计数=sum(num),
累计数=(select sum(num) from 表 where id=a.id and date<=a.date)
from 表 a group by id,date
order by id,date

--删除测试环境
Drop Table 表