使用场景:统计支出表中某人某年的每个月的支出情况
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'