SQL考你一下,不知算不算高难度的SQL,100分!大家帮忙看,关于ROllUP的,如果觉得分少还可以送的(100分)

有一数据表(PlanForm)
linename id customercode model deductnumber
---------------------------------------------------- --------------------
A 6402 CJ RV0902NA-20F-B10K 8350
A 6403 WXN RV1212NB-10FI-A10K 4000
A 6404 WXN RV1212NB-CC-15FI-B10K 4000
B 6405 F005 RV1610GPH-20KQD-B50K 11000
B 6406 HWE RV1610GPH×2-20KQ-B50K 320
B 6407 HWE RV1610GPH×3-20KQ-B50K 140
B 6408 F003 RV1610GPH×3-15KQD-B50K 500
C 6409 HM RV1212G-20F-A20K 2500
C 6410 ALR RV1615G-25KQ-B50K 2000
C 6411 ALR RV1615N-25KQ-B20K 1000
C 6442 S007 RV1212G-15F-B50K 7000
D 6425 F005 RV1610GPH-20KQD-B50K 14000
D 6426 L007 RV1610GPH-15F-B10K 97
D 6427 L007 RV1610GPH-25F-B10K 1600

我想得到这种效果
linename id model customercode summ
------------------------------------------------ ----------------------------------------------
A 6404 RV1212NB-CC-15FI-B10K WXN 4000
A 6403 RV1212NB-10FI-A10K WXN 4000
A 6402 RV0902NA-20F-B10K CJ 8350
小计 NULL NULL NULL 16350
B 6408 RV1610GPH×3-15KQD-B50K F003 500
B 6407 RV1610GPH×3-20KQ-B50K HWE 140
B 6406 RV1610GPH×2-20KQ-B50K HWE 320
B 6405 RV1610GPH-20KQD-B50K F005 11000
小计 NULL NULL NULL 11960
C 6442 RV1212G-15F-B50K S007 7000
C 6411 RV1615N-25KQ-B20K ALR 1000
C 6410 RV1615G-25KQ-B50K ALR 2000
C 6409 RV1212G-20F-A20K HM 2500
小计 NULL NULL NULL 12500
D 6427 RV1610GPH-25F-B10K L007 1600
D 6426 RV1610GPH-15F-B10K L007 97
D 6425 RV1610GPH-20KQD-B50K F005 14000
小计 NULL NULL NULL 15697
合计 NULL NULL NULL 174435

up

select
linename=case
when a.linename<>'' and isnull(a.id,'')='' and isnull(model,'')='' and isnull(a.customercode,'')='' then '小计'
when isnull(a.type,'')='' and isnull(a.price,0)=0 and isnull(a.id,'')='' and isnull(model,'')='' and isnull(a.customercode,'')='' then '合计'
else a.linename
end
,id, model,customercode,summ
from
(
select linename, id, customercode, model, sum(deductnumber) as summ
from PlanForm
group by linename, id, customercode, model
with rollup
) as a

如果id值没有重复可以正常运行
select case
when (grouping(linename)=0) and (grouping(id)=1) then '小计'
when (grouping(linename)=1) and (grouping(id)=1) then '合计'
when (grouping(id)=0) then linename
end as linename,
case
when (grouping(id)=1) then ''
when (grouping(id)=0) then id
end as
id,
case
when (grouping(id)=1) then ''
when (grouping(id)=0) then max(model)
end as model,
case
when (grouping(id)=1) then ''
when (grouping(id)=0) then max(customercode)
end as customercode,
sum(summ) as summ
from PlanForm
group by linename,id with rollup

谢谢"狼"了
没试过Xxfeng的不知道行不行,但是狼的可以

select case when (grouping(linename)=0) and (grouping(id)=1) then '小计'
when (grouping(linename)=1) and (grouping(id)=1) then '合计'
when (grouping(id)=0) then linename
end as linename,
case when (grouping(id)=1) then ''
when (grouping(id)=0) then id
end as id,
case when (grouping(id)=1) then ''
when (grouping(id)=0) then max(model)
end as model,
case when (grouping(id)=1) then ''
when (grouping(id)=0) then max(customercode)
end as customercode,
max(customername) as 客户名,
sum(deductnumber) as summ
from PlanForm where plandate='2002-9-2'
group by linename,[id] with rollup

我整理了一个大家可以看清楚一点