锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle如何按月统计 oracle按月份统计

IT技术Yyyy

oraclesql每月登录次数统计最佳回答：select sum(cnt)/第N月分oracle如何按月统计的天数 from select 日期oracle如何按月统计 ， count(*）cnt from (select 日期，用户名 from 登录表 where 月份
oracle 只根据年份（如2011）查询每月统计数据不知道oracle如何按月统计你oracle如何按月统计的时间和参数time分别是什么类型的oracle如何按月统计，只能暂时考虑为vchar型的 。
select
to_char(时间,'yyyy-mm') as 每月时间
, sum(统计数据)
from 测试表
where substr(to_char(to_date('2011/7','YYYY/MM'),'YYYY/MM/DD'),0,4) = substr(to_char(to_timestamp('2011/7/1 18:42:43','YYYY/MM/DD hh24:mi:ss'),'YYYY/MM/DD hh24:mi:ss'),0,4)
group by to_char(时间,'yyyy-mm')
-----下面的这个假设时间是date型的oracle如何按月统计 ， 参数time是timestamp型
select
to_char(时间,'yyyy-mm') as 每月时间
, sum(统计数据)
from 测试表
where substr(to_char(时间,'yyyy-mm'),0,4) = substr(to_char(time,'YYYY/MM/DD hh24:mi:ss'),0,4)
group by to_char(时间,'yyyy-mm')
oracle数据库中怎么查询当前年每月的天数，按自然月统计，谢谢！楼上的只统计了一个月的吧
WITH t AS
(SELECTROWNUM
FROM DUAL
CONNECT BY ROWNUM = 12)
SELECTTO_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中group by按月分组统计创建测试表
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
(
IDNUMBER(11) not null,
PAY_NAME VARCHAR2(100),
AUTHORVARCHAR2(100),
TYPE_IDNUMBER(11),
PRICEFLOAT, --金额
BRIEFVARCHAR2(1000),
USERIDNUMBER(11), --用户ID
TIMESDATE, --时间
PRIMARY KEY(ID)
【oracle如何按月统计 oracle按月份统计】)
第二步：插入数据
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如何按月统计的介绍就聊到这里吧 ， 感谢你花时间阅读本站内容 ， 更多关于oracle按月份统计、oracle如何按月统计的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：盐城仓库管理erp系统厂家，盐城仓库保管招聘

    下一篇：html5播放页面，html5播放器怎么用