求一条sql语句，在线等

[louifox]

有如下表：
table1：
id 产品id 入库数量 入库日期
1 1 10 2004-12-11
2 2 15 200412-1
3 1 20 2004-05-22
4 2 5 2004-12-25
要得到以下的查询结果
：
产品id 入库数量 入库日期
1 10 2004-12-11
1 20 2004-05-22
小计 30
2 15 200412-1
2 5 2004-12-25
小计 20
这样的sql该怎么写？

[Andy__Huang]

drop table tb

create table tb(id int,產品id numeric(10),入庫數量 numeric(10),入庫日期 datetime)
Insert into tb
select '1','1','10','2004-12-11'
union all select '2','2','15','2004-12-11'
union all select '3','1','20','2004-05-22'
union all select '4','2','5','2004-12-25'

select * from tb

--查詢結果
select 產品id=case when 入庫日期 is null then '小計' else 產品id end ,入庫數量,入庫日期 from tb
union all
select 產品id,入庫數量=sum(入庫數量),null from tb group by 產品id
order by 1,3 desc

產品id 入庫數量 入庫日期
----------------------------------------------------
1 10 2004-12-11 00:00:00.000
1 20 2004-05-22 00:00:00.000
1 30 NULL
2 5 2004-12-25 00:00:00.000
2 15 2004-12-11 00:00:00.000
2 20 NULL

[Andy__Huang]

改一點

create table tb(id int,產品id numeric(10),入庫數量 numeric(10),入庫日期 datetime)
Insert into tb
select '1','1','10','2004-12-11'
union all select '2','2','15','2004-12-11'
union all select '3','1','20','2004-05-22'
union all select '4','2','5','2004-12-25'

select * from tb

--查詢結果


select 產品id=case when 入庫日期 is null then '小計' else cast(產品id as varchar) end ,入庫數量,入庫日期 from
(select 產品id,入庫數量=sum(入庫數量),入庫日期
from tb group by 產品id ,入庫日期 with rollup)a
where 產品id is not null

產品id 入庫數量 入庫日期
----------------------------------------------------
1 20 2004-05-22 00:00:00.000
1 10 2004-12-11 00:00:00.000
小計 30 NULL
2 15 2004-12-11 00:00:00.000
2 5 2004-12-25 00:00:00.000
小計 20 NULL

[vinsonshen]

select cast(產品id as varchar) as 產品id,入庫數量,入庫日期 from tb
union all
select cast(產品id as varchar)+'小计',sum(入庫數量),null from tb group by 產品id
order by 產品id

[NinGoo]

select 產品id,sum(入庫數量) as 入库数量,入庫日期 from tb group by 產品id,入庫日期
union all
select 產品id+'小计',sum(入庫數量),null from tb group by 產品id
order by 1,3 desc

[vinsonshen]

若你的入庫日期 格式是：2004-12-30 21:51:41

则：
select cast(產品id as varchar) as 產品id,入庫數量,convert(varchar(10),入庫日期,120) as 入庫日期 from tb
union all
select cast(產品id as varchar)+'小计',sum(入庫數量),null from tb group by 產品id
order by 產品id

[vinsonshen]

若你的入庫日期 格式是：2004-12-30 21:51:41

则：
select cast(產品id as varchar) as 產品id,入庫數量,convert(varchar(10),入庫日期,120) as 入庫日期 from tb
union all
select cast(產品id as varchar)+'小计',sum(入庫數量),null from tb group by 產品id
order by 產品id

[vinsonshen]

楼上的，你直接id相加会出错的，若id是int类型时

[NinGoo]

呵呵,我直接把產品id列设计成nvarchar了