--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 2-- 2 奖项数量 0 1 1 0 1 1 0 1 1 1--初始数据如下：SELECT ZJJX, SUM(CGSL), SUM(CGJEH), SUM(SBSL), SUM(SBJEH) FROM ( SELECT ZJJX, COUNT(ZJJE) CGSL, SUM(ZJJE) CGJEH, 0 SBSL, 0 SBJEH FROM YW_ZJFPJL where substr(to_char(zjsj, 'yyyy-mm-dd hh24:mi:ss'), 0, 7) = '2016-10' GROUP BY ZJJX UNION ALL SELECT ZJJX, 0 CGSL, 0 CGJEH, COUNT(ZJJE) SBSL, SUM(ZJJE) SBJEH FROM YW_FJCWJL where substr(to_char(zjsj, 'yyyy-mm-dd hh24:mi:ss'), 0, 7) = '2016-10' GROUP BY ZJJX) GROUP BY ZJJX;/* 一等奖 1 200 0 0 三等奖 16 160 5 50 四等奖 28 140 23 115 五等奖 52 104 33 66*/--以下sql完成的哦--奖项金额完成的SELECT * FROM (SELECT '奖项金额(成功)', SUM(CASE WHEN ZJJX = '一等奖' THEN SUM(ZJJE) ELSE 0 END) 一等奖成功金额, SUM(CASE WHEN ZJJX = '二等奖' THEN SUM(ZJJE) ELSE 0 END) 二等奖成功金额, SUM(CASE WHEN ZJJX = '三等奖' THEN SUM(ZJJE) ELSE 0 END) 三等奖成功金额, SUM(CASE WHEN ZJJX = '四等奖' THEN SUM(ZJJE) ELSE 0 END) 四等奖成功金额, SUM(CASE WHEN ZJJX = '五等奖' THEN SUM(ZJJE) ELSE 0 END) 五等奖成功金额 FROM YW_ZJFPJL T WHERE to_char(zjsj,'yyyymmdd')='20161017' GROUP BY ZJJX), (SELECT '奖项金额(失败)', SUM(CASE WHEN ZJJX = '一等奖' THEN SUM(ZJJE) ELSE 0 END) 一等奖失败金额, SUM(CASE WHEN ZJJX = '二等奖' THEN SUM(ZJJE) ELSE 0 END) 二等奖失败金额, SUM(CASE WHEN ZJJX = '三等奖' THEN SUM(ZJJE) ELSE 0 END) 三等奖失败金额, SUM(CASE WHEN ZJJX = '四等奖' THEN SUM(ZJJE) ELSE 0 END) 四等奖失败金额, SUM(CASE WHEN ZJJX = '五等奖' THEN SUM(ZJJE) ELSE 0 END) 五等奖失败金额 FROM YW_FJCWJL T WHERE to_char(zjsj,'yyyymmdd')='20161017' GROUP BY ZJJX); --输出结果： 1 奖项金额(成功) 200 0 160 140 104 奖项金额(失败) 0 0 50 115 66 --奖项数量完成的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 T WHERE to_char(zjsj,'yyyymmdd')='20161017' 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,'yyyymmdd')='20161017' GROUP BY ZJJX); --输出结果： 奖项数量(成功) 1 0 16 28 52 奖项数量(失败) 0 0 5 23 33
转载于:https://www.cnblogs.com/iyoume2008/p/6122895.html
