关于 sql server 2000 排序问题

qdwangqun 2010-09-17 04:35:21
有一表:
----
MeasureID SerialNo MeasureName TimePointZone
Z00002009000120 000008 利润总额 01
Z00002009000121 000001 预算值 01
Z00002009000018 000007 煤炭货运量 03
Z00002009000022 000008 同国内行业 03
Z00002009000022 000005 吞吐量 02
----
其中:TimePointZont字段的"01"对应"月"、"02"对应"年"、"03"对应"季",若按照"TimePointZone"字段进行排序后,会出现以下结果表:
-----------
MeasureID SerialNo MeasureName TimePointZone
Z00002009000120 000008 利润总额 月
Z00002009000121 000001 预算值 月
Z00002009000022 000005 吞吐量 年
Z00002009000018 000007 煤炭货运量 季
Z00002009000022 000008 同国内行业 季
-----------
但如果要实现如下结果表的话,SQL查询语句就如何写呢?
谢谢!
...全文
64 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2010-09-17
create table tb (MeasureID varchar(20),SerialNo  varchar(20),MeasureName  varchar(20),TimePointZone  varchar(20))  
insert into tb values('Z00002009000120', '000008', '利润总额' , '01')
insert into tb values('Z00002009000121', '000001', '预算值' , '01')
insert into tb values('Z00002009000018', '000007', '煤炭货运量', '03')
insert into tb values('Z00002009000022', '000008', '同国内行业', '03')
insert into tb values('Z00002009000022', '000005', '吞吐量' , '02')
go

select MeasureID ,SerialNo ,MeasureName ,TimePointZone =
case when TimePointZone = '01' then '月'
when TimePointZone = '02' then '年'
when TimePointZone = '03' then '季' end
from tb
order by case when TimePointZone = '01' then 1 when TimePointZone = '02' then 2 else 3 end

select MeasureID ,SerialNo ,MeasureName ,TimePointZone =
case when TimePointZone = '01' then '月'
when TimePointZone = '02' then '年'
when TimePointZone = '03' then '季' end
from tb
order by TimePointZone

drop table tb

/*
MeasureID SerialNo MeasureName TimePointZone
-------------------- -------------------- -------------------- -------------
Z00002009000120 000008 利润总额 月
Z00002009000121 000001 预算值 月
Z00002009000022 000005 吞吐量 年
Z00002009000018 000007 煤炭货运量 季
Z00002009000022 000008 同国内行业 季

(所影响的行数为 5 行)
*/
回复
dawugui 2010-09-17
select MeasureID ,SerialNo ,MeasureName ,TimePointZone = 
case when TimePointZone = '01' then '月'
when TimePointZone = '02' then '年'
when TimePointZone = '03' then '季' end
from tb
order by case when TimePointZone = '01' then 1 when TimePointZone = '02' then 2 else 3 end
回复
zsh0809 2010-09-17
select MeasureID ,SerialNo ,MeasureName ,
case TimePointZone when '01' then '月'
when '02' then '年' when ‘03’ then ‘季’end TimePointZone
from tb
order by TimePointZone
回复
zsh0809 2010-09-17
select MeasureID ,SerialNo ,MeasureName ,
case TimePointZone when '01' then '月'
when '02' then '年' when ‘03’ then ‘季’end TimePointZone
from tb
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-17 04:35
社区公告
暂无公告