首页 新闻 搜索 专区 学院

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

 

itman020的主页 itman020 | 初学一级 | 园豆:0
提问于:2008-09-22 17:32
< >
分享
最佳答案
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字段对 的唯一性、求和处理。

分步走,或许能实现。

难道我想简单了?

我心里大致有这样的一个存储过程(上面的视图化作过程中的临时表),如果楼主不理解,俺抽空弄个伪代码。

陛下 | 老鸟四级 |园豆:3938 | 2008-09-22 20:02
其他回答(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

丁学 | 园豆:18530 (专家六级) | 2008-09-22 17:48
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册