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

Question

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   Price2006-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        110Thx

Answer 1

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

Answer 2

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,50select  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_iddrop 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 行）

Answer 3

不能和楼主提供的结果完全相同.这样试试:
----创建测试数据
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

Answer 4

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