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

  SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two'ELSE 'other'END FROM test;