sql按照总和进行按降和按升序排序?
0
[已解决问题]
解决于 2008-09-26 10:55
数据库存的数据：
Item YearMonth Num
电冰箱 200801 10
空调 200801 20
彩电 200801 50
电冰箱 200802 30
空调 200802 40
彩电 200802 30
电冰箱 200803 50
空调 200803 30
彩电 200803 10
电冰箱 200804 20
空调 200804 20
彩电 200804 35
电冰箱 200805 31
空调 200805 25
彩电 200805 18
电冰箱 200806 5
空调 200806 12
彩电 200806 20
电冰箱 200807 13
空调 200807 25
彩电 200807 10
电冰箱 200808 16
空调 200808 30
彩电 200808 21
需要的形式
Item 200801 200802 200803 200804 200805 200806 200807 200808 200809 200810 200811 200812
彩电 50 30 10 35 18 20 10 21 0 0 0 0
电冰箱 10 30 50 20 31 5 13 16 0 0 0 0
空调 20 40 30 20 25 12 25 30 0 0 0 0
我已通过行转换成列，实现了以上的形式，但是还需要让将上面的形式，按照月份的总和，进行按降和按升序排序。
请问怎么办呢？
----
第一步：
select Item,
sum(case YearMonth when '200801' then Num else 0 end) as '200801',
sum(case YearMonth when '200802' then Num else 0 end) as '200802',
sum(case YearMonth when '200803' then Num else 0 end) as '200803',
sum(case YearMonth when '200804' then Num else 0 end) as '200804',
sum(case YearMonth when '200805' then Num else 0 end) as '200805',
sum(case YearMonth when '200806' then Num else 0 end) as '200806',
sum(case YearMonth when '200807' then Num else 0 end) as '200807',
sum(case YearMonth when '200808' then Num else 0 end) as '200808',
sum(case YearMonth when '200809' then Num else 0 end) as '200809',
sum(case YearMonth when '200810' then Num else 0 end) as '200810',
sum(case YearMonth when '200811' then Num else 0 end) as '200811',
sum(case YearMonth when '200812' then Num else 0 end) as '200812'
from T_TEST group by Item
最佳答案
0
我只说一个思路，还未知是否可行：
先对源表做视图 vew_Item_Time：
Item_Time Number
电冰箱_200801 10
空调_200801 20
彩电_200801 50
电冰箱_200801 10
空调_200801 20
彩电_200801 50
……
再对上视图做二次视图 view_Distinct_Item_Time
Distinct_Item_Time SumNumber
电冰箱_200801 20
空调_200801 40
彩电_200801 100
……
最后对上面的数据进行排序并输出.
意思不过是，对 Item-Time字段对 的唯一性、求和处理。
分步走，或许能实现。
难道我想简单了？
我心里大致有这样的一个存储过程(上面的视图化作过程中的临时表)，如果楼主不理解，俺抽空弄个伪代码。
其他回答(1)
0
汗~~~你这个应该放到程序中去处理，不应该在这里，这不是SQL的强项，行列转换让SQL做很难为情的，比如说再增加一个月.......
你这个可以再添加一列，就是直接sum(Num) as 'total'
然后就可以 order by total了
select Item,
sum(case YearMonth when '200801' then Num else 0 end) as '200801',
sum(case YearMonth when '200802' then Num else 0 end) as '200802',
sum(case YearMonth when '200803' then Num else 0 end) as '200803',
sum(case YearMonth when '200804' then Num else 0 end) as '200804',
sum(case YearMonth when '200805' then Num else 0 end) as '200805',
sum(case YearMonth when '200806' then Num else 0 end) as '200806',
sum(case YearMonth when '200807' then Num else 0 end) as '200807',
sum(case YearMonth when '200808' then Num else 0 end) as '200808',
sum(case YearMonth when '200809' then Num else 0 end) as '200809',
sum(case YearMonth when '200810' then Num else 0 end) as '200810',
sum(case YearMonth when '200811' then Num else 0 end) as '200811',
sum(case YearMonth when '200812' then Num else 0 end) as '200812',
SUM(Num) as 'Total'
from T_TEST group by Item order by Total desc
