oracle统计一年中每个月的数据

最新推荐文章于 2021-04-06 11:45:03 发布

liuguocheng

最新推荐文章于 2021-04-06 11:45:03 发布

阅读量1.5k

点赞数

分类专栏： oralce 按月统计

本文链接：https://blog.csdn.net/liuguocheng/article/details/12581137

版权

oralce 同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

按月

1 篇文章 0 订阅

订阅专栏

统计

1 篇文章 0 订阅

订阅专栏

第一种写法：

SELECT SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 1, B.PRICE, 0)) AS A,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 2, B.PRICE, 0)) AS B,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 3, B.PRICE, 0)) AS C,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 4, B.PRICE, 0)) AS D,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 5, B.PRICE, 0)) AS E,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 6, B.PRICE, 0)) AS F,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 7, B.PRICE, 0)) AS G,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 8, B.PRICE, 0)) AS H,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 9, B.PRICE, 0)) AS I,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 10, B.PRICE, 0)) AS J,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 11, B.PRICE, 0)) AS K,
       SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 12, B.PRICE, 0)) AS L
  FROM PAY B
 WHERE USERID = 1
   AND EXTRACT(YEAR FROM B.TIMES) = '2012'

加一种写法：

SELECT SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '01', B.PRICE, 0)) M1,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '02', B.PRICE, 0)) M2,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '03', B.PRICE, 0)) M3,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '04', B.PRICE, 0)) M4,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '05', B.PRICE, 0)) M5,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '06', B.PRICE, 0)) M6,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '07', B.PRICE, 0)) M7,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '08', B.PRICE, 0)) M8,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '09', B.PRICE, 0)) M9,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '10', B.PRICE, 0)) M10,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '11', B.PRICE, 0)) M11,
       SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '12', B.PRICE, 0)) M12
  FROM PAY B
 WHERE USERID = 1
   AND TO_CHAR(B.TIMES, 'yyyy') = '2012'