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

最新推荐文章于 2023-04-28 11:03:46 发布

原创最新推荐文章于 2023-04-28 11:03:46 发布 · 1.6k 阅读

1 ·

CC 4.0 BY-SA版权

oralce 同时被 3 个专栏收录

5 篇文章

订阅专栏

按月

1 篇文章

订阅专栏

统计

1 篇文章

订阅专栏

本文介绍了一种使用SQL查询来统计特定用户在2012年各月份的支付总额的方法。通过两种不同的写法实现了相同的功能，一种是使用EXTRACT函数提取月份，另一种是使用TO_CHAR函数进行日期格式化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一种写法：

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'