重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
--按照楼主的意思 楼主应该有两个疑问 一个是分组汇总 一个是在正常的日期取出年月
网站建设哪家好,找创新互联建站!专注于网页设计、网站建设、微信开发、微信小程序、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了峰峰矿免费建站欢迎大家使用!
--帮你写好 有什么问题可以随时问我
select to_char(日期字段,'yyyy-mm') as 年月,count(1) as 天数 from 表名
group by to_char(日期字段,'yyyy-mm') ;
创建测试表
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');
结果
这是递归写法的问题,在8,9i升级新版本过程中经常遇到类似问题.这个在10g,11g以后跑应该都没问题,在9i按以下方法写可以实现:
SELECT TO_CHAR (ADD_MONTHS (TO_DATE ('2011-03', 'yyyy-mm'), ROWNUM - 1),'YYYY-MM') AS yearmonth FROM all_objects
WHERE ROWNUM =(SELECT MONTHS_BETWEEN (TO_DATE ('2012-07', 'yyyy-mm'),TO_DATE ('2011-03', 'yyyy-mm')) FROM DUAL)
select to_char(last_day(sysdate),'dd') from dual
也可以将sysdate替换为任意时间,以计算当天所在月份天数。
第一步:创建表
--支出表
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'
第四步:输出结果
--希望解决了楼主的问题
select bm,sum(case when to_char(time,'MM')='01' then fz else 0 end ) 1月份fz合计,
sum(case when to_char(time,'MM')='02' then fz else 0 end ) 2月份fz合计,
sum(case when to_char(time,'MM')='03' then fz else 0 end ) 3月份fz合计,
sum(case when to_char(time,'MM')='04' then fz else 0 end ) 4月份fz合计,
sum(case when to_char(time,'MM')='05' then fz else 0 end ) 5月份fz合计,
sum(case when to_char(time,'MM')='06' then fz else 0 end ) 6月份fz合计,
sum(case when to_char(time,'MM')='07' then fz else 0 end ) 7月份fz合计,
sum(case when to_char(time,'MM')='08' then fz else 0 end ) 8月份fz合计,
sum(case when to_char(time,'MM')='09' then fz else 0 end ) 9月份fz合计,
sum(case when to_char(time,'MM')='10' then fz else 0 end ) 10月份fz合计,
sum(case when to_char(time,'MM')='11' then fz else 0 end ) 11月份fz合计,
sum(case when to_char(time,'MM')='12' then fz else 0 end ) 12月份fz合计 from bmfz
group by bm