关于 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查询语句就如何写呢?
谢谢!
...全文
96 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
「已注销」 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
「已注销」 2010-09-17
  • 打赏
  • 举报
回复
select MeasureID ,SerialNo ,MeasureName ,
case TimePointZone when '01' then '月'
when '02' then '年' when ‘03’ then ‘季’end TimePointZone
from tb

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧