oracle 统计每年每月数据

发布网友发布时间：2022-04-09 10:51

共4个回答

热心网友时间：2022-04-09 12:20

第一步：创建表

--支出表
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)
)

第二步：插入数据

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;

第三步：执行分组查询

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'

第四步：输出结果

热心网友时间：2022-04-09 13:38

select year,
sum(case when month=1 then amount else 0 end) m1,
sum(case when month=2 then amount else 0 end) m2,
sum(case when month=3 then amount else 0 end) m3,
sum(case when month=4 then amount else 0 end) m4
from 表名
group by year

热心网友时间：2022-04-09 15:13

解题思路你可以参考一下行转列，具体SQL如下：

select year, sum(m1) m1, sum(m2) m2, sum(m3) m3, sum(m4) m4
  from (select year,
               decode(month, 1, amount, 0) m1,
               decode(month, 2, amount, 0) m2,
               decode(month, 3, amount, 0) m3,
               decode(month, 4, amount, 0) m4
          from table_name) a
group by year
order by year
-- 1. 首先使用decode函数进行行转列
-- 2. 再按年进行分组，统计每个月份的值

有问题请追问，希望可以帮到你

热心网友时间：2022-04-09 17:04

你是怎么写的？