怎样优化case when then？

[kissemail]

各位大虾，请帮我优化一下以下代码，太长了,可以一个case解决它吗？我试了很久都不行，谢谢。

select *,
(case when A<=11 then '1' 
when A between 12 and 22 then '2' 
when A between 23 and 33 then '3' 
end
) as H, 
(case when B<=11 then '1' 
when B between 12 and 22 then '2' 
when B between 23 and 33 then '3' 
end
) as I,
(case when C<=11 then '1' 
when C between 12 and 22 then '2' 
when C between 23 and 33 then '3' 
end
) as J,
(case when D<=11 then '1' 
when D between 12 and 22 then '2' 
when D between 23 and 33 then '3' 
end
) as K,
(case when E<=11 then '1' 
when E between 12 and 22 then '2' 
when E between 23 and 33 then '3' 
end
) as L,
(case when E<=11 then '1' 
when F between 12 and 22 then '2' 
when F between 23 and 33 then '3' 
end
) as N   
from All_N order by NO DESC

[kissemail]

[Quote=引用 1 楼 qianjin036a 的回复:]

SQL code
select (x-1)/11+1 as y from all_n orcer by no desc

这里的x 是你的 abcdef 各列,y 就是 hijkln各列.
条件是x必须>=1,否则,要用符号函数处理一下再作相应的计算.
[/Quote]

这样虽然不用case，但要用多个select，不过也谢谢你的思路！

[-晴天]

select (x-1)/11+1 as y from all_n orcer by no desc

这里的x 是你的 abcdef 各列,y 就是 hijkln各列.
条件是x必须>=1,否则,要用符号函数处理一下再作相应的计算.

[kissemail]

[Quote=引用 3 楼 ssp2009 的回复:]

SQL code
select *,
(case when A<=11 then '1' when A<=22 then '2' when A<=33 then '3' end) as H，
--。。。。。
from All_N order by NO DESC

一个case肯定是搞不定的，因为你这是多列，不过你可以参照我上边写的，不需要在判断>12，when，then本身就带有break功……
[/Quote]

了解，参照你的思路，可以减少代码，谢谢

[-晴天]

还有一个要求就是x 是 int 类型的.

[快溜]

select *,
(case when A<=11 then '1' when A<=22 then '2' when A<=33 then '3' end) as H，
--。。。。。
from All_N order by NO DESC

一个case肯定是搞不定的，因为你这是多列，不过你可以参照我上边写的，不需要在判断>12，when，then本身就带有break功能，优先满足第一个，一次类推。