分不多，求一查询算法,Thanks

[My_first]

Order_id Product_id Adddate Group_id Price
R0730060001001 #10001 2006-07-30 1 20
R0730060001002 #10002 2006-07-30 1 30
R0730060002001 #10003 2006-07-30 2 10
R0730060002002 #10004 2006-07-30 2 50

用怎样的SQL语句才可以显示为　
Adddate Order_id Product_id Price

2006-07-30 R0730060001001 #10001 20
2006-07-30 R0730060001002 #10002 30
Null Null Null Null
2006-07-30 R0730060002001 #10003 10
2006-07-30 R0730060002002 #10004 50
Subtotal Null Null 110

Thx

[turenjie]

看不出来有什么不同，就是中间加了一行null值

[mylkzy]

楼主还不如将要达到的目的说出来,像这样的数据没看出来有什么用

[撸大湿]

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 行）

[hellowork]

不能和楼主提供的结果完全相同.这样试试:
----创建测试数据
declare @t table(Order_id varchar(20),Product_id varchar(20),Adddate datetime,Group_id int,Price int)
insert @t
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
Product_id = case when(grouping(Product_id) = 1) then 'SubTotal' else Product_id end,
Adddate = case when product_id is null then null else max(Adddate) end,
Order_id = case when product_id is null then null else max(Order_id) end,
sum(Price) as price
from @t group by group_id,Product_id with rollup

[hellowork]

使用时可以将
when product_id is null
都替换为
when(grouping(Product_id) = 1)
二者在此是等价的.