横向显示-CSDN博客

一、使用CASE语句
1、无求和运算
-------------------------------------------------------------------------------------
SELECT a.sno,
         MAX (CASE WHEN b.cname = 'c' THEN c.score ELSE NULL END)
            "c语言成绩",
         MAX (CASE WHEN b.cname = 'j' THEN c.score ELSE NULL END)
            "j语言成绩",
         MAX (CASE WHEN b.cname = 'd' THEN c.score ELSE NULL END)
            "d语言成绩"
    FROM student a, course b, score c
   WHERE a.sno = c.sno AND b.cno = c.cno
GROUP BY a.sno;

SNO   c语言成绩   j语言成绩   d语言成绩
----- ----------- ----------- -----------
    1          87          98          85
    2          85                      95
    3                      87
-------------------------------------------------------------------------------------
SELECT sno
FROM ( SELECT a.sno,
                 MAX (CASE WHEN b.cname = 'c' THEN c.score ELSE NULL END) c,
                 MAX (CASE WHEN b.cname = 'j' THEN c.score ELSE NULL END) j,
                 MAX (CASE WHEN b.cname = 'd' THEN c.score ELSE NULL END) d,
                 MAX (CASE WHEN b.cname = 's' THEN c.score ELSE NULL END) s
            FROM student a, course b, score c
           WHERE a.sno = c.sno AND b.cno = c.cno
        GROUP BY a.sno)
WHERE c IS NOT NULL AND j IS NOT NULL AND d IS NOT NULL AND s IS NOT NULL;
-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
select a.cust_no,
       a.name,
       a.town || a.address || a.building address,
       a.dis_no,
       a.cust_type_no,
       max (
          case when b.cardholder_no = 1 then b.cardholder_name else null end)
          name1,
       max (
          case when b.cardholder_no = 1 then b.mobile_phone_no else null end)
          phone1,
       max (
          case when b.cardholder_no = 2 then b.cardholder_name else null end)
          name2,
       max (
          case when b.cardholder_no = 2 then b.mobile_phone_no else null end)
          phone2,
       max (
          case when b.cardholder_no = 3 then b.cardholder_name else null end)
          name3,
       max (
          case when b.cardholder_no = 3 then b.mobile_phone_no else null end)
          phone3,
       max (
          case when b.cardholder_no = 4 then b.cardholder_name else null end)
          name4,
       max (
          case when b.cardholder_no = 4 then b.mobile_phone_no else null end)
          phone4,
       max (
          case when b.cardholder_no = 5 then b.cardholder_name else null end)
          name5,
       max (
          case when b.cardholder_no = 5 then b.mobile_phone_no else null end)
          phone5
from cust a, cardholder b
where a.cust_no = b.cust_no and a.cust_no in ()
group by a.cust_no,a.name,a.town,a.address,a.building,a.dis_no,a.cust_type_no;
-------------------------------------------------------------------------------------

2、有求和运算
---------------------------------------------------------------------------
select distinct
         b.dept_no,
         sum (
            case
               when c.move_date like sysdate - 318 then c.sales_amnt
               else 0
            end)
            sales_1,
         sum (
            case
               when c.move_date like sysdate - 317 then c.sales_amnt
               else 0
            end)
            sales_2
    from article a, art_grp b, art_movement c
   where     a.art_grp_no = b.art_grp_no
         and a.art_no = c.art_no
         and c.move_kind = 7
group by b.dept_no;
---------------------------------------------------------------------------

3、条件
---------------------------------------------------------------------------
select b.dept_no,
         a.art_grp_no,
         a.art_no,
         a.descr,
         a.suppl_no,
         a.sell_pr,
         a.stock,
         a.on_order,
         a.dms,
         a.last_delday,
         a.last_saleday,
         sum (case when c.move_kind = 7 then c.move_qty else 0 end) move_qty,
         sum (c.sales_amnt)
    from article a, art_grp b, art_movement c
   where     a.art_grp_no = b.art_grp_no
         and a.art_no = c.art_no
         and b.dept_no in (11, 12, 13)
         and c.move_date between '20081108' and '20081109'
group by b.dept_no,
         a.art_grp_no,
         a.art_no,
         a.descr,
         a.suppl_no,
         a.sell_pr,
         a.stock,
         a.on_order,
         a.dms,
         a.last_delday,
         a.last_saleday
order by b.dept_no,
         a.art_grp_no,
         a.suppl_no,
         a.art_no;
---------------------------------------------------------------------------

二、使用DECODE语句
1、无求和运算
---------------------------------------------------------------------------
select a.time_zone,
         a.dis_no,
         a.cust_no,
         a.name,
         max (decode (b.cardholder_no, 1, b.cardholder_name)) n1,
         max (decode (b.cardholder_no, 2, b.cardholder_name)) n2,
         max (decode (b.cardholder_no, 3, b.cardholder_name)) n3,
         max (decode (b.cardholder_no, 4, b.cardholder_name)) n4,
         max (decode (b.cardholder_no, 5, b.cardholder_name)) n5
    from cust a, cardholder b
   where a.cust_no = b.cust_no and a.reg_date like sysdate - 1
group by a.time_zone,
         a.dis_no,
         a.cust_no,
         a.name;
---------------------------------------------------------------------------

2、有求和运算
---------------------------------------------------------------------------
select b.dept_no,
         sum (
            decode (c.move_date,
                    to_date ('20080101', 'yyyymmdd'), c.sales_amnt))
            amnt1,
         sum (
            decode (c.move_date,
                    to_date ('20080102', 'yyyymmdd'), c.sales_amnt))
            amnt2
    from article a, art_grp b, art_movement c
   where     a.art_grp_no = b.art_grp_no
         and a.art_no = c.art_no
         and c.move_kind = 7
group by b.dept_no;
---------------------------------------------------------------------------

---------------------------------------------------------------------------
select b.dept_no,
         a.art_grp_no,
         a.art_no,
         a.descr,
         sum (
            case
               when c.move_date = to_date ('20090801', 'yyyymmdd')
               then
                  c.move_qty
               else
                  0
            end)
            qty0801,
         sum (
            case
               when c.move_date = to_date ('20090801', 'yyyymmdd')
               then
                  c.sales_amnt
               else
                  0
            end)
            amnt0801,
         sum (
            case
               when c.move_date = to_date ('20090811', 'yyyymmdd')
               then
                  c.move_qty
               else
                  0
            end)
            qty0811,
         sum (
            case
               when c.move_date = to_date ('20090811', 'yyyymmdd')
               then
                  c.sales_amnt
               else
                  0
            end)
            amnt0811
    from article a,
         art_grp b,
         (select *
            from art_movement
           where move_date = to_date ('20090801', 'yyyymmdd') and move_kind = 7
          union
          select *
            from art_movement
           where move_date = to_date ('20090811', 'yyyymmdd') and move_kind = 7) c
   where a.art_grp_no = b.art_grp_no and a.art_no = c.art_no
group by b.dept_no,
         a.art_grp_no,
         a.art_no,
         a.descr
order by b.dept_no,
         a.art_grp_no,
         a.art_no,
         a.descr;
---------------------------------------------------------------------------