关于SQL的case when魅力

最近去面试碰到了两道关于数据库的笔试题目：

       1：有member表(m_id  int,m_sex varchar(20))其中m_sex有三个不同值('男','女','null'),

            要求用一条SQL语句把member表的m_sex字段更新成m_sex的值('男')变成'女'，

            '女'变成'男','null'不变；

            答：update member
                    set m_sex =case m_sex when '女' then '男' when '男' then '女' else 'null'
                    end

 

     2：有一张表table1，有三个字段分别是：年度，季度，数量；现在数据如下：

          年度，季度，数量

           1991    1        23

           1991    2        32

           1991    3        15

           1991    4        20

           1992    1        25

           1992    2       65

          要求用一条SQL语句查出结果为：

          年份  1季度  2季度  3季度  4季度

           1991    23        32       15        20 

           1992    25        65        0         0    

      答:select b.Year as '年份',sum(b.quarter1) as '1季度',sum(b.quarter2) as '2季度',
            sum(b.quarter3) as '3季度',sum(b.quarter4) as '4季度' from(
            select a.Year,
            case a.quarter when 1 then a.Nums else 0 end as quarter1,
            case a.quarter when 2 then a.Nums else 0 end as quarter2,
            case a.quarter when 3 then a.Nums else 0 end as quarter3,
            case a.quarter when 4 then a.Nums else 0 end as quarter4
            from table1 as a)b
            group by b.Year