create table #mytb (Order_id char(14),Product_id char(6),
Adddate datetime,Group_id int,Price decimal(10,0))
insert into #mytb
select 'R0730060001001','#10001','2006-07-30',1,20
union all select 'R0730060001002','#10002','2006-07-30',1,30
union all select 'R0730060002001','#10003','2006-07-30',2,10
union all select 'R0730060002002','#10004','2006-07-30',2,50
select a.Adddate,
a.Order_id,
b.Product_id,
b.Price from #mytb a
right join (
select left(Order_id,11)Order_id,Product_id,sum(price) price from #mytb group by left(Order_id,11),Product_id with rollup
) b
on a.Product_id=b.Product_id
drop table #mytb
结果
(所影响的行数为 4 行)
Adddate Order_id Product_id Price
------------------------------------------------------ -------------- ---------- ----------------------------------------
2006-07-30 00:00:00.000 R0730060001001 #10001 20
2006-07-30 00:00:00.000 R0730060001002 #10002 30
NULL NULL NULL 50
2006-07-30 00:00:00.000 R0730060002001 #10003 10
2006-07-30 00:00:00.000 R0730060002002 #10004 50
NULL NULL NULL 60
NULL NULL NULL 110
(所影响的行数为 7 行)