oracle如何按月统计 oracle按月分组统计
oracle中group by按月分组统计
创建测试表
我们提供的服务有：网站设计制作、网站设计、微信公众号开发、网站优化、网站认证、于洪ssl等。为成百上千企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的于洪网站制作公司
create table test
(姓名 varchar2(10),
访问时间 date,
进入时间 date,
离开时间 date);
insert into test values ('张三',to_date('2013-02-03 15:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-03 15:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-03 15:33:22','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('李四',to_date('2013-02-04 15:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-04 18:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-04 18:53:22','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('王武',to_date('2013-02-04 15:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-05 17:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-05 18:23:22','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('张三',to_date('2013-02-04 15:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-06 11:23:22','yyyy-mm-dd hh24:mi:ss'),to_date('2013-02-06 14:23:22','yyyy-mm-dd hh24:mi:ss'));
运行
select 姓名,
to_char(访问时间,'yyyy-mm') 访问月份,
to_char(trunc(round(sum(离开时间-进入时间)*1440)/60))||'小时'||to_char(round(sum((离开时间-进入时间)*1440))-trunc(round(sum(离开时间-进入时间)*1440)/60)*60)||'分钟' 时间汇总,
count(*) 访问次数 from test
group by 姓名,to_char(访问时间,'yyyy-mm');
结果
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)
)
第二步：插入数据
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'
第四步：输出结果
oracle数据库中怎么查询当前年每月的天数，按自然月统计，谢谢！
楼上的只统计了一个月的吧
WITH t AS
(SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM = 12)
SELECT TO_CHAR (LAST_DAY (TO_DATE ( TO_CHAR (SYSDATE, 'YYYY')
|| LPAD (TO_CHAR (ROWNUM), 2, 0),
'YYYYMM'
)
),
'YYYY-MM'
)
|| '月',
TO_CHAR (LAST_DAY (TO_DATE ( TO_CHAR (SYSDATE, 'YYYY')
|| LPAD (TO_CHAR (ROWNUM), 2, 0),
'YYYYMM'
)
),
'DD'
)
|| '天'
FROM t
直接全选复制粘贴运行
文章名称：oracle如何按月统计 oracle按月分组统计
文章URL：http://ybzwz.com/article/hjoooi.html
