问题求助：统计排序。

[老七]

表结构：
日期 ，供应商，物料，数量，价格，金额。
求排序SQL.
格式要求：
排名 供应商 物料 数量 金额
1 aa 50
A 10 10
B 20 20
C 10 20
2 bb D 2 40
………
总计 9999

[老七]

明早揭帖，谢谢rea1gz(冒牌realgz V0.3)

[Well]

不知道楼主的9999是怎么算来的

[rea1gz]

未测试

select case when type=1 then 排名 else null end as 排名,
供应商,物料,数量,金额
from (
select 排名=1+(select count(*) from (select 供应商,sum(金额) as 金额 from tablename group by 供应商) as t
where 金额>(select sum(金额) from tablename where 供应商=b.供应商)
),
null as 供应商,物料,sum(数量) as 数量,sum(金额) as 金额,type=2
from tablename b group by 供应商,物料
union all
select 排名=1+(select count(*) from (select 供应商,sum(金额) as 金额 from tablename group by 供应商) as t
where 金额>sum(a.金额)
),
供应商,null as 物料,null as 数量,sum(金额) as 金额,type=1
from tablename a group by 供应商
union all
select 1+count(distinct 供应商) as 排名,'总计' as 供应商,null as 物料,null as 数量,sum(金额) as 金额,type=3
from tablename
) as x
order by 排名,type,物料

[rea1gz]

测试

declare @t table (日期 datetime,供应商 Nvarchar(30),物料 Nvarchar(30),数量 int,价格 int,金额 int)
insert @t
select '2006-1-1','aa','A',10,1,10
union all
select '2006-1-2','aa','B',10,1,10
union all
select '2006-1-2','bb','D',2,20,40
union all
select '2006-1-3','aa','C',10,2,20
union all
select '2006-1-4','aa','B',10,1,10

select case when type=1 then 排名 else null end as 排名,
供应商,物料,数量,金额
from (
select 排名=1+(select count(*) from (select 供应商,sum(金额) as 金额 from @t group by 供应商) as t
where 金额>(select sum(金额) from @t where 供应商=b.供应商)
),
null as 供应商,物料,sum(数量) as 数量,sum(金额) as 金额,type=2
from @t b group by 供应商,物料
union all
select 排名=1+(select count(*) from (select 供应商,sum(金额) as 金额 from @t group by 供应商) as t
where 金额>sum(a.金额)
),
供应商,null as 物料,null as 数量,sum(金额) as 金额,type=1
from @t a group by 供应商
union all
select 1+count(distinct 供应商) as 排名,N'总计' as 供应商,null as 物料,null as 数量,sum(金额) as 金额,type=3
from @t
) as x
order by x.排名*1,type,物料

--结果
排名 供应商 物料 数量 金额
----------- ------------------------------ ------------------------------ ----------- -----------
1 aa NULL NULL 50
NULL NULL A 10 10
NULL NULL B 20 20
NULL NULL C 10 20
2 bb NULL NULL 40
NULL NULL D 2 40
NULL 总计 NULL NULL 90

（所影响的行数为 7 行）

[rea1gz]

发现个奇怪的问题：

select case when type=1 then 排名 else null end as 排名,
供应商,物料,数量,金额
from (
--为表述方便，中间省略
) as x
order by x.排名,type,物料

的排序不是想象的

select 排名,
供应商,物料,数量,金额
from (
--为表述方便，中间省略
) as x
order by x.排名,type,物料

的排序是想象的

没办法只好

select case when type=1 then 排名 else null end as 排名,
供应商,物料,数量,金额
from (
--为表述方便，中间省略
) as x
order by x.排名*1,type,物料

sql server的复杂排序经常结果和想象的不同，必须测试才能知道是否达到要求

[老七]

效果较好，下午我再实际运用一下。

[老七]

是一个不确定的数，不是有......吗