日期:2014-05-18  浏览次数:20496 次

case when的疑问
数据库里有1,2,3,4,5共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3
第一种:
select * from o 
 order by case id when 4 then 1
  when 5 then 2
  when 1 then 3
  when 2 then 4
  when 3 then 5 end
第二种:
select * from o 
order by case when id = 4 then 1 end,
 case when id = 5 then 2 end,
 case when id = 1 then 3 end,
 case when id = 2 then 4 end,
 case when id = 3 then 5 end 
第一种方式可以实现功能,但第二种方式却不能,为什么?这两种方式有什么区别???

------解决方案--------------------
第二种
SQL code
select * from o  
order by 
 case 
  when id = 4 then 1 
  when id = 5 then 2 
  when id = 1 then 3 
  when id = 2 then 4
  when id = 3 then 5 
 end

------解决方案--------------------
还有一种方法
SQL code
select * from o  
order by 
  charindex(','+ltrim(id)+',',',4,5,1,2,3,')

------解决方案--------------------
SQL code

--正确
select * from o  
order by 
 case 
  when id = 4 then 1 
  when id = 5 then 2 
  when id = 1 then 3 
  when id = 2 then 4
  when id = 3 then 5 
 end 

--错误
select * from o  
order by case when id = 4 then 1 end,--end当id<>4时,语句就停止了
 case when id = 5 then 2 end,
 case when id = 1 then 3 end,
 case when id = 2 then 4 end,
 case when id = 3 then 5 end