日期:2014-05-19  浏览次数:20447 次

合计 排序(100)!
统计数据在加了     With   rollup     结果这样

F         F1       F2     F3
A         2       2       1
B         2       5       3
C         1       3       2
D         4       1       1
E         4       6       1
合计   13     17     7

想得到以下得结果
F         F2       F2     F3
A         2       2       1
B         5       2       3
C         3       1       2
D         1       4       1
E         6       4       1
合计   17     13     7         <--按这个排序输出   得到排名



------解决方案--------------------
怎么看不懂啊
有2個F2嗎?
給個例子你參考吧
/*
按GoodId分組合計
*/
declare @t table(goodid varchar(10),date1 datetime,bh int,Num1 int,Num2 int)
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-3 ',1101,50,10
insert into @t select '000001 ', '2005-1-4 ',1101,50,10
insert into @t select '000001 ', '2005-2-1 ',1101,50,10
insert into @t select '000001 ', '2005-2-2 ',1101,50,10
insert into @t select '000002 ', '2005-1-1 ',1101,50,10
insert into @t select '000002 ', '2005-1-2 ',1101,50,10
insert into @t select '000002 ', '2005-1-3 ',1101,50,10
insert into @t select '000002 ', '2005-1-4 ',1101,50,10
insert into @t select '000002 ', '2005-2-1 ',1101,50,10
insert into @t select '000002 ', '2005-2-2 ',1101,50,10
insert into @t select '000003 ', '2005-1-1 ',1101,50,10
insert into @t select '000003 ', '2005-1-2 ',1101,50,10
insert into @t select '000003 ', '2005-1-3 ',1101,50,10
insert into @t select '000003 ', '2005-1-4 ',1101,50,10
insert into @t select '000003 ', '2005-2-1 ',1101,50,10
insert into @t select '000003 ', '2005-2-2 ',1101,50,10


select
(case
when goodid is null then '合計 '
when date1 is null and goodid is not null then '小計 '
else goodid
end) as goodid,
date1,
bh,
sum(Num1) as Num1,
sum(Num2) as Num2
from
@t
group by
goodid,date1,bh
with rollup
having
grouping(bh)=0 or
grouping(date1)=1

/*
結果:
goodid date1 bh Num1 Num2
---------- ----------------------- ----------- ----------- -----------
000001 2005-01-01 00:00:00.000 1101 50 10
000001 2005-01-02 00:00:00.000 1101 50 10
000001 2005-01-03 00:00:00.000 1101 50 10
000001 2005-01-04 00:00:00.000 1101 50 1