case when then else

//                  http://blog.csdn.net/lenotang/archive/2008/08/21/2806658.aspx

 

SELECT
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END AS Range,
Count(*)
FROM titles
GROUP BY
        CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
GO

 

select 中用了case

group by 中重复一次

结果:

Average                                 9 

Bargain                                4 

Gift to impress relatives    3 

Unpriced                            2