DB2 CASE WHEN的使用(表的旋转)
在一行中显示出这个销售经理1~12月每月的销售数量:
SELECT SALES_MGR,
MAX(CASE MONTH WHEN 1 THEN SALES_AMT ELSE NULL END) AS JAN,
MAX(CASE MONTH WHEN 2 THEN SALES_AMT ELSE NULL END) AS FEB,
MAX(CASE MONTH WHEN 3 THEN SALES_AMT ELSE NULL END) AS MAR,
MAX(CASE MONTH WHEN 4 THEN SALES_AMT ELSE NULL END) AS APR,
MAX(CASE MONTH WHEN 5 THEN SALES_AMT ELSE NULL END) AS MAY,
MAX(CASE MONTH WHEN 6 THEN SALES_AMT ELSE NULL END) AS JUN,
MAX(CASE MONTH WHEN 7 THEN SALES_AMT ELSE NULL END) AS JUL,
MAX(CASE MONTH WHEN 8 THEN SALES_AMT ELSE NULL END) AS AUG,
MAX(CASE MONTH WHEN 9 THEN SALES_AMT ELSE NULL END) AS SEP,
MAX(CASE MONTH WHEN 10 THEN SALES_AMT ELSE NULL END) AS OTC,
MAX(CASE MONTH WHEN 11 THEN SALES_AMT ELSE NULL END) AS NOV,
MAX(CASE MONTH WHEN 12 THEN SALES_AMT ELSE NULL END) AS DEC,
FROM SALES
WHERE SALES_MGR=?
AND TEAR=?;
SELECT SALES_MGR,
MAX(CASE MONTH WHEN 1 THEN SALES_AMT ELSE NULL END) AS JAN,
MAX(CASE MONTH WHEN 2 THEN SALES_AMT ELSE NULL END) AS FEB,
MAX(CASE MONTH WHEN 3 THEN SALES_AMT ELSE NULL END) AS MAR,
MAX(CASE MONTH WHEN 4 THEN SALES_AMT ELSE NULL END) AS APR,
MAX(CASE MONTH WHEN 5 THEN SALES_AMT ELSE NULL END) AS MAY,
MAX(CASE MONTH WHEN 6 THEN SALES_AMT ELSE NULL END) AS JUN,
MAX(CASE MONTH WHEN 7 THEN SALES_AMT ELSE NULL END) AS JUL,
MAX(CASE MONTH WHEN 8 THEN SALES_AMT ELSE NULL END) AS AUG,
MAX(CASE MONTH WHEN 9 THEN SALES_AMT ELSE NULL END) AS SEP,
MAX(CASE MONTH WHEN 10 THEN SALES_AMT ELSE NULL END) AS OTC,
MAX(CASE MONTH WHEN 11 THEN SALES_AMT ELSE NULL END) AS NOV,
MAX(CASE MONTH WHEN 12 THEN SALES_AMT ELSE NULL END) AS DEC,
FROM SALES
WHERE SALES_MGR=?
AND TEAR=?;
作者:Rick Carter
出处:http://pains.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。