SQL Server case when

CASE WHEN 在 SELECT 中的使用

# Case搜索函数
select a,case when len(b)>5 then '' else b end b from table
# 简单Case函数
select case a when 'name' then '姓名' when 'high' then '身高' else null end a from table

CASE WHEN 在 WHERE 中的使用

SELECT *
FROM   tablename
WHERE  field IN ( 10, 20 )
AND ( CASE WHEN field1 = 'M' AND field2 <> 'N' THEN 0 
            ELSE 1
            END 
) = 1 

CASE WHEN 在 ORDER BY 中的使用

select * from tablename
order by 
    case when field1 = 'a' then 1
        when field1 = 'b' then 0
        else 2 end,
    case when field2 = 'd' then 0
        when field2 = 'r' then 1
        when field2 = 't' then 2
        when field2 = 'g' then 3
        when field2 = 'n' then 4
        when field2 = 'y' then 5
        else 6 end

CASE WHEN 在 GROUP BY 中的使用

SELECT 
sum(case when C=1 then D else 0 end)/sum(case when C=1 then E else 0 end) as C1,
sum(case when C=2 then D else 0 end)/sum(case when C=2 then E else 0 end) as C2,b
from test.newrain
group by b