oracle统计一年中每个月的数据
使用场景:统计支出表中某人某年的每个月的支出情况
1.创建表如下(oracle)
--支出表
create table PAY
(
ID NUMBER(11) not null,
PAY_NAME VARCHAR2(100),
AUTHOR VARCHAR2(100),
TYPE_ID NUMBER(11),
PRICE FLOAT, --金额
BRIEF VARCHAR2(1000),
USERID NUMBER(11), --用户ID
TIMES DATE, --时间
PRIMARY KEY(ID)
)
2.插入测试数据
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (1, '1', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (0, '3', '3', 1, 3, '3', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (2, '2', '2', 1, 2, '2343444', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (6, '6', '1', 0, 4000, '1', 1, to_date('12-08-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (5, '5', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (4, '4', '1', 0, 100, '1', 1, to_date('12-09-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (7, '4', '1', 0, 566, '1', 1, to_date('03-03-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (11, '4', '1', 0, 566, '1', 1, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (10, '4', '1', 0, 566, '1', 1, to_date('03-05-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (9, '4', '1', 0, 566, '1', 1, to_date('03-06-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (8, '4', '1', 0, 566, '1', 1, to_date('03-07-2012', 'dd-mm-yyyy'));
commit;
3.统计sql(查询userid为1的用户2012年的每个月支出情况)
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'
4.统计结果如下
A B C D E F G H I J K L
0 0 566 566 566 566 566 4000 100 7 0 0
5.另外一种写法:对于时间的操作也可以用下面这个
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'
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步