《一》合并两个结果集，并且两个结果集的数据 根据条目自动归为一行结果集1 如下：SQL> select t1.fplx,t1.djje from yw_zjfpjl t1 ; FPLX DJJE-------- -----------------一等奖 10一等奖 20一等奖 30一等奖 40一等奖 50一等奖 60二等奖 10二等奖 30二等奖 40二等奖 50二等奖 10二等奖 10四等奖 10四等奖 10四等奖 10四等奖 10四等奖 10四等奖 10三等奖 10三等奖 10三等奖 10三等奖 10三等奖 10三等奖 10五等奖 10五等奖 10五等奖 10五等奖 10五等奖 10五等奖 10结果集2 如下：SQL> select t2.fplx,t2.djje from yw_fjcwjl t2; FPLX DJJE-------- -----------------一等奖 20一等奖 30一等奖 40一等奖 50二等奖 10二等奖 30二等奖 40二等奖 50二等奖 10四等奖 10四等奖 10四等奖 10四等奖 10四等奖 10三等奖 10三等奖 10三等奖 10三等奖 10五等奖 10五等奖 10五等奖 10五等奖 10五等奖 10五等奖 10合并后结果集如下:SQL> select fplx, sum(jecg), sum(jehcg), sum(jecw), sum(jehcw) from (select fplx, count(zjje) jecg, sum(zjje) jehcg, 0 jecw, 0 jehcw from yw_zjfpjl group by fplx union all select fplx, 0 jecw, 0 jehcw, count(zjje) jecw, sum(zjje) jehcw from yw_fjcwjl group by fplx) group by fplx order by fplx asc;FPLX SUM(JECG) SUM(JEHCG) SUM(JECW) SUM(JEHCW)----------- ----------- ----------- ---------- ------------二等奖 6 150 6 150三等奖 6 60 6 60四等奖 6 60 6 60五等奖 6 60 6 60一等奖 6 210 6 210《二》
原结果：
SELECT 1 NUM, '奖项数量(成功)', SUM(CASE WHEN ZJJX= '一等奖' THEN COUNT(ZJJE) ELSE 0 END), SUM(CASE WHEN ZJJX= '二等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '三等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '四等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '五等奖' THEN COUNT(ZJJE) ELSE 0 END) FROM YW_ZJFPJL T --WHERE to_char(zjsj)='20111129' GROUP BY ZJJX UNION ALL SELECT 2 NUM, '奖项数量(失败)', SUM(CASE WHEN ZJJX= '一等奖' THEN COUNT(ZJJE) ELSE 0 END), SUM(CASE WHEN ZJJX= '二等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '三等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '四等奖' THEN COUNT(ZJJE) ELSE 0 END) , SUM(CASE WHEN ZJJX= '五等奖' THEN COUNT(ZJJE) ELSE 0 END) FROM YW_FJCWJL T --WHERE to_char(zjsj)='20111129' GROUP BY ZJJX；
1 奖项数量(成功) 1 0 16 28 52 2 奖项数量(失败) 0 0 5 23 33
修改之后 两行合并为一行
SELECT * FROM (SELECT '奖项数量(成功)', SUM(CASE WHEN ZJJX= '一等奖' THEN COUNT(ZJJE) ELSE 0 END) 一等奖成功, SUM(CASE WHEN ZJJX= '二等奖' THEN COUNT(ZJJE) ELSE 0 END) 二等奖成功, SUM(CASE WHEN ZJJX= '三等奖' THEN COUNT(ZJJE) ELSE 0 END) 三等奖成功, SUM(CASE WHEN ZJJX= '四等奖' THEN COUNT(ZJJE) ELSE 0 END) 四等奖成功, SUM(CASE WHEN ZJJX= '五等奖' THEN COUNT(ZJJE) ELSE 0 END) 五等奖成功 FROM YW_ZJFPJL --WHERE to_char(zjsj)='20111129' GROUP BY ZJJX ), (SELECT '奖项数量(失败)', SUM(CASE WHEN ZJJX= '一等奖' THEN COUNT(ZJJE) ELSE 0 END) 一等奖失败, SUM(CASE WHEN ZJJX= '二等奖' THEN COUNT(ZJJE) ELSE 0 END) 二等奖失败, SUM(CASE WHEN ZJJX= '三等奖' THEN COUNT(ZJJE) ELSE 0 END) 三等奖失败, SUM(CASE WHEN ZJJX= '四等奖' THEN COUNT(ZJJE) ELSE 0 END) 四等奖失败, SUM(CASE WHEN ZJJX= '五等奖' THEN COUNT(ZJJE) ELSE 0 END) 五等奖失败 FROM YW_FJCWJL T --WHERE to_char(zjsj)='20111129' GROUP BY ZJJX ); --1 奖项数量(成功) 1 0 16 28 52 奖项数量(失败) 0 0 5 23 33
《三 经典版的case when》
SELECT * FROM (SELECT 1 NUM, '奖项金额', SUM(CASE WHEN DJMC= '一等奖' AND CGBZ=0 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '二等奖' AND CGBZ=0 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '三等奖' AND CGBZ=0 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '四等奖' AND CGBZ=0 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '五等奖' AND CGBZ=0 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '一等奖' AND CGBZ=1 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '二等奖' AND CGBZ=1 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '三等奖' AND CGBZ=1 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '四等奖' AND CGBZ=1 THEN DJJE ELSE 0 END) , SUM(CASE WHEN DJMC= '五等奖' AND CGBZ=1 THEN DJJE ELSE 0 END) FROM XXDZMX T WHERE DZYF=20111129 AND ZFLX=0 UNION ALL SELECT 2 NUM, '奖项数量', SUM(CASE WHEN DJMC= '一等奖' AND CGBZ=0 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '二等奖' AND CGBZ=0 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '三等奖' AND CGBZ=0 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '四等奖' AND CGBZ=0 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '五等奖' AND CGBZ=0 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '一等奖' AND CGBZ=1 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '二等奖' AND CGBZ=1 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '三等奖' AND CGBZ=1 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '四等奖' AND CGBZ=1 THEN 1 ELSE 0 END), SUM(CASE WHEN DJMC= '五等奖' AND CGBZ=1 THEN 1 ELSE 0 END) FROM XXDZMX T WHERE DZYF=20111129 AND ZFLX=0 ) ORDER BY NUM 1 奖项金额 0 50 10 0 2 200 0 10 5 22 奖项数量 0 1 1 0 1 1 0 1 1 1
转载于:https://www.cnblogs.com/iyoume2008/p/6038997.html
