使用场景:根据年统计月渲染按年统计的柱状图

Oracle 统计

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block
var foo = 'bar';

create table PAY

一个简单的表

id	NUMBER(11) PRIMARY KEY
PAY_NAME	VARCHAR2(100)
AUTHOR	VARCHAR2(100),
TYPE_ID	NUMBER(11),
PRICE	FLOAT, --金额,
BRIEF	VARCHAR2(1000),
USERID	NUMBER(11), --用户ID,

TIMES DATE, --时间

插入测试数据

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

注释

按年统计将每个月的数据统计出来，月份没数据的会自动补零方便前台渲染

另外一种写法：对于时间的操作也可以用下面这个

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’