合计 排序（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 <--按这个排序输出 得到排名
[解决办法]
怎么看不懂啊
有２個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 10
000001 2005-02-01 00:00:00.000 1101 50 10
000001 2005-02-02 00:00:00.000 1101 50 10
小計 NULL NULL 300 60
000002 2005-01-01 00:00:00.000 1101 50 10
000002 2005-01-02 00:00:00.000 1101 50 10
000002 2005-01-03 00:00:00.000 1101 50 10
000002 2005-01-04 00:00:00.000 1101 50 10
000002 2005-02-01 00:00:00.000 1101 50 10
000002 2005-02-02 00:00:00.000 1101 50 10
小計 NULL NULL 300 60
000003 2005-01-01 00:00:00.000 1101 50 10
000003 2005-01-02 00:00:00.000 1101 50 10
000003 2005-01-03 00:00:00.000 1101 50 10
000003 2005-01-04 00:00:00.000 1101 50 10
000003 2005-02-01 00:00:00.000 1101 50 10
000003 2005-02-02 00:00:00.000 1101 50 10
小計 NULL NULL 300 60
合計 NULL NULL 900 180
*/
[解决办法]
你要的是这样的嘛？
create table ty (F nvarchar(10), F1 int, F2 int , F3 int )
insert into ty select 'A ', 2 , 2 , 1
union select 'B ', 2 , 5 , 3
union select 'C ', 1 , 3 , 2
union select 'D ', 4 , 1 , 1
union select 'E ', 4 , 6 , 1
insert into ty select 'A ', 0 , 0 , 0
union select 'B ', 0 , 0 , 0
union select 'C ', 0 , 0 , 0
union select 'D ', 0 , 0 , 0
union select 'E ', 0 , 0 , 0
select (case when f is null then N '合计 ' else f end ) f, sum(f1) as f1, sum(f2) as f2, sum(f3) as f3
from ty
group by f WITH ROLLUP
--这样是你的第一个的结果
F F1 F2 F3
A221
B253
C132
D411
E461
合计13178
select (case when f is null then N '合计 ' else f end ) f, sum(f2) as f2,sum(f1) as f1, sum(f3) as f3
from ty
group by f WITH ROLLUP
--第二个的结果
F F2 F1 F3
A221
B523
C312
D141
E641
合计17138
[解决办法]
declare @s nvarchar(4000)
set @s= ' '
select @s=@s+ ', '+quotename(type)+ '=isnull(sum(case type when '+quotename(type, ' ' ' ')+ ' then [money] end),0) '
from a
group by type type ORDER BY SUM([Money]) DESC --加上此句就搞定了
