CASE WHEN及基本sql

case例子

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

3：表sale中有两个字段productid,product.表product中有id,productid,表produ中有id,product:

       现在数据如下

          productid  product

             1               0

             2               0

             0               3

             0               4

             0               5

product       +------+-----------+
| id   | productid |
+------+-----------+
|   11 |         1 |
|   13 |         3 |
|   14 |         4 |
+------+-----------+

produ

+------+---------+
| id   | product |
+------+---------+
|   12 |       2 |
|   15 |       5 |
+------+---------+

求

+-----------+---------+------+
| productid | product | id   |
+-----------+---------+------+
|         1 |       0 |   11 |
|         0 |       2 |   12 |
|         3 |       0 |   13 |
|         4 |       0 |   14 |
|         0 |       5 |   15 |
+-----------+---------+------+

       select s.productid,s.product,case 'id'
       when s.productid=0 then (select p.id from produ as p where s.product=p.product)
      else (select pi.id from product as pi where s.productid=pi.productid)
      end
      from
       sale as s,product as pi,produ as p;

如果想让表头根据自己显示，用as +要显示的名。