sql利用CASE WHEN THEN ELSE END选择搜索并分组数据的一个实例

该实例实现不同余额卡的分段

SELECT  
        CASE WHEN cdmye <= 100 THEN '1 <100' 
             WHEN cdmye > 100 AND cdmye <= 500  THEN '2 100-500' 
             WHEN cdmye > 500 AND cdmye <= 2000  THEN '3 500-2000' 
             WHEN cdmye > 200 AND cdmye <= 10000 THEN '4 2000-10000' 
             WHEN cdmye >10000 THEN '5 >10000'
        ELSE NULL END as "等级", 
        COUNT(*) "张数",SUM(cdmye) "金额"
FROM    CARDMAIN_MZK_BK 
GROUP BY 
        CASE WHEN cdmye <= 100 THEN '1 <100' 
             WHEN cdmye > 100 AND cdmye <= 500  THEN '2 100-500' 
             WHEN cdmye > 500 AND cdmye <= 2000  THEN '3 500-2000' 
             WHEN cdmye > 200 AND cdmye <= 10000 THEN '4 2000-10000' 
             WHEN cdmye >10000 THEN '5 >10000'
        ELSE NULL END
order by 
        CASE WHEN cdmye <= 100 THEN '1 <100' 
             WHEN cdmye > 100 AND cdmye <= 500  THEN '2 100-500' 
             WHEN cdmye > 500 AND cdmye <= 2000  THEN '3 500-2000' 
             WHEN cdmye > 200 AND cdmye <= 10000 THEN '4 2000-10000' 
             WHEN cdmye >10000 THEN '5 >10000'
        ELSE NULL END