17,086
社区成员
发帖
与我相关
我的任务
分享
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput ON
SQL> --创建过程:
SQL> CREATE OR REPLACE PROCEDURE p_get_info(resuleSet OUT sys_refcursor)
2 IS
3 BEGIN
4 OPEN resuleSet FOR
5 SELECT
6 Decode(Grouping(equipName),1,'总计',equipName) "设备种类",
7 Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) "1月",
8 Sum(Decode(To_Char(cheindate,'mm'),'02',1,0)) "2月",
9 Sum(Decode(To_Char(cheindate,'mm'),'03',1,0)) "3月",
10 Sum(Decode(to_char(cheindate,'q'),1,1,0)) "一季度",
11 Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) "4月",
12 Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) "5月",
13 Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) "6月",
14 Sum(Decode(to_char(cheindate,'q'),2,1,0)) "二季度",
15 Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) "7月",
16 Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) "8月",
17 Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) "9月",
18 Sum(Decode(to_char(cheindate,'q'),3,1,0)) "三季度",
19 Sum(Decode(To_Char(cheindate,'mm'),'010',1,0)) "10月",
20 Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) "11月",
21 Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) "12月",
22 Sum(Decode(to_char(cheindate,'q'),4,1,0)) "四季度",
23 Count(1) "总计"
24 FROM tab1 GROUP BY rollup(equipName);
25 END;
26 /
Procedure created.
SQL> --测试过程
SQL> var cur refcursor
SQL> exec p_get_info(:cur);
PL/SQL procedure successfully completed.
SQL> --结果:
SQL> print cur
--结果:
设备种类 1月 2月 3月 一季度 4月 5月 6月 二季度 7月 8月 9月 三季度 10月 11月 12月 四季度 总计
---------------------------------------------------------------------------------------------------------
交流电动转辙机 1 1 0 2 0 1 0 1 0 0 1 1 0 0 1 1 5
直流电动转辙机 2 1 1 4 0 1 2 3 0 1 0 1 0 0 0 0 8
总计 3 2 1 6 0 2 2 4 0 1 1 2 0 0 1 1 13
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput on
SQL> alter session set nls_date_format='yyyy-mm-dd' ;
Session altered.
SQL> select * from tab1;
ID EQUIPNAME CHEINDATE
-------- ------------------------------------------ ----------
t001 交流电动转辙机 2010-01-10
t001 交流电动转辙机 2010-02-11
t002 直流电动转辙机 2010-01-12
t002 直流电动转辙机 2010-02-13
t002 直流电动转辙机 2010-01-11
t002 直流电动转辙机 2010-03-11
t001 交流电动转辙机 2010-05-11
t002 直流电动转辙机 2010-06-12
t002 直流电动转辙机 2010-05-13
t002 直流电动转辙机 2010-06-11
t002 直流电动转辙机 2010-08-11
ID EQUIPNAME CHEINDATE
-------- ------------------------------------------ ----------
t001 交流电动转辙机 2010-09-11
t001 交流电动转辙机 2010-12-11
13 rows selected.
SQL> --带参数的过程:
SQL> CREATE OR REPLACE PROCEDURE p_get_info(v_id IN VARCHAR2, --直流交流类型
2 v_cheindate IN VARCHAR2, --时间
3 resuleSet OUT sys_refcursor --返回结果集
4 )
5 IS
6 sql_str VARCHAR2(4000);
7 BEGIN
8 sql_str:=
9 'SELECT
10 Decode(Grouping(equipName),1,''总计'',equipName) as equipName,
11 Sum(Decode(To_Char(cheindate,''mm''),''01'',1,0)) mon1,
12 Sum(Decode(To_Char(cheindate,''mm''),''02'',1,0)) mon2,
13 Sum(Decode(To_Char(cheindate,''mm''),''03'',1,0)) mon3,
14 Sum(Decode(to_char(cheindate,''q''),1,1,0)) one_quarter,
15 Sum(Decode(To_Char(cheindate,''mm''),''04'',1,0)) mon4,
16 Sum(Decode(To_Char(cheindate,''mm''),''05'',1,0)) mon5,
17 Sum(Decode(To_Char(cheindate,''mm''),''06'',1,0)) mon6,
18 Sum(Decode(to_char(cheindate,''q''),2,1,0)) two_quarter,
19 Sum(Decode(To_Char(cheindate,''mm''),''07'',1,0)) mon7,
20 Sum(Decode(To_Char(cheindate,''mm''),''08'',1,0)) mon8,
21 Sum(Decode(To_Char(cheindate,''mm''),''09'',1,0)) mon9,
22 Sum(Decode(to_char(cheindate,''q''),3,1,0)) three_quarter,
23 Sum(Decode(To_Char(cheindate,''mm''),''10'',1,0)) mon10,
24 Sum(Decode(To_Char(cheindate,''mm''),''11'',1,0)) mon11,
25 Sum(Decode(To_Char(cheindate,''mm''),''12'',1,0)) mon12,
26 Sum(Decode(to_char(cheindate,''q''),4,1,0)) four_quarter,
27 Count(1) cnt
28 FROM (SELECT * FROM tab1 WHERE id='''||v_id||''' AND cheindate <= to_date('''||v_chei
ndate||''',''yyyy-mm-dd''))
29 GROUP BY rollup(equipName)';
30 --Dbms_Output.put_line(sql_str);
31 OPEN resuleSet FOR sql_str;
32
33 END;
34 /
Procedure created.
SQL> var cur refcursor
SQL> exec p_get_info('t001','2010-06-10',:cur);
PL/SQL procedure successfully completed.
SQL> print cur
EQUIPNAME MON1 MON2 MON3 ONE_QUARTER MON4 MON5 MON6 TWO_QUARTER MON7 MON8 MON9 THREE_QUARTER MON10 MON11 MON12 FOUR_QUARTER CNT
-----------------------------------------------------------------------
交流电动转辙机 1 1 0 2 0 1 0 1 0 0 0 0 0 0 0 0 3
总计 1 1 0 2 0 1 0 1 0 0 0 0 0 0 0 0 3
--突然想起来,to_char(sysdate,'q')这个可以返回季度
SQL> select to_char(to_date('20100310','yyyymmdd'),'q') from dual;
T
-
1
select equipName,
sum(case when to_char(cheindate,'dd')=1 then 1 else 0 end) "1月",
sum(case when to_char(cheindate,'dd')=2 then 1 else 0 end) "2月",
sum(case when to_char(cheindate,'dd')=3 then 1 else 0 end) "3月",
sum(case when to_char(cheindate,'q')=1 then 1 else 0 end) "1季度",
sum(case when to_char(cheindate,'dd')=4 then 1 else 0 end) "4月",
sum(case when to_char(cheindate,'dd')=5 then 1 else 0 end) "5月",
sum(case when to_char(cheindate,'dd')=6 then 1 else 0 end) "6月",
sum(case when to_char(cheindate,'q')=2 then 1 else 0 end) "2季度",
sum(case when to_char(cheindate,'dd')=7 then 1 else 0 end) "7月",
sum(case when to_char(cheindate,'dd')=8 then 1 else 0 end) "8月",
sum(case when to_char(cheindate,'dd')=9 then 1 else 0 end) "9月",
sum(case when to_char(cheindate,'q')=3 then 1 else 0 end) "3季度",
sum(case when to_char(cheindate,'dd')=10 then 1 else 0 end) "10月",
sum(case when to_char(cheindate,'dd')=11 then 1 else 0 end) "11月",
sum(case when to_char(cheindate,'dd')=12 then 1 else 0 end) "12月",
sum(case when to_char(cheindate,'q')=4 then 1 else 0 end) "4季度",
count(*) "合计"
from tab1
group by equipName
--估计楼主是写错了
--下面这样是不是你想的?
WITH tab1 AS(
SELECT 't001' id,'交流电动转辙机' equipName,To_Date('2010-01-10','yyyy-mm-dd') cheindate FROM dual UNION ALL
SELECT 't001', '交流电动转辙机',To_Date('2010-02-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-01-12','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-02-13','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-01-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-03-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't001', '交流电动转辙机',To_Date('2010-05-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-06-12','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-05-13','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-06-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't002', '直流电动转辙机',To_Date('2010-08-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't001', '交流电动转辙机',To_Date('2010-09-11','yyyy-mm-dd') FROM dual UNION ALL
SELECT 't001', '交流电动转辙机',To_Date('2010-12-11','yyyy-mm-dd') FROM dual
)
--以上是测试数据
SELECT
Decode(Grouping(equipName),1,'总计',equipName) "设备种类",
Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) "1月",
Sum(Decode(To_Char(cheindate,'mm'),'02',1,0)) "2月",
Sum(Decode(To_Char(cheindate,'mm'),'03',1,0)) "3月",
Sum(case when to_char(cheindate,'mm')<=3 then 1 else 0 end) "一季度",
Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) "4月",
Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) "5月",
Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) "6月",
Sum(case when to_char(cheindate,'mm')<=6 and to_char(cheindate,'mm')>3 then 1 else 0 end) "二季度",
Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) "7月",
Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) "8月",
Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) "9月",
Sum(case when to_char(cheindate,'mm')<=9 and to_char(cheindate,'mm')>6 then 1 else 0 end) "三季度",
Sum(Decode(To_Char(cheindate,'mm'),'010',1,0)) "10月",
Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) "11月",
Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) "12月",
Sum(case when to_char(cheindate,'mm')<=12 and to_char(cheindate,'mm')>9 then 1 else 0 end) "四季度",
Count(1) "总计"
FROM tab1 GROUP BY rollup(equipName)
--结果:
设备种类 1月 2月 3月 一季度 4月 5月 6月 二季度 7月 8月 9月 三季度 10月 11月 12月 四季度 总计
---------------------------------------------------------------------------------------------------------
交流电动转辙机 1 1 0 2 0 1 0 1 0 0 1 1 0 0 1 1 5
直流电动转辙机 2 1 1 4 0 1 2 3 0 1 0 1 0 0 0 0 8
总计 3 2 1 6 0 2 2 4 0 1 1 2 0 0 1 1 13
--这样?
select equipName,
sum(case when to_char(cheindate,'dd')=1 then 1 else 0 end) 1月,
sum(case when to_char(cheindate,'dd')=2 then 1 else 0 end) 2月,
sum(case when to_char(cheindate,'dd')=3 then 1 else 0 end) 3月,
sum(case when to_char(cheindate,'dd')<=3 then 1 else 0 end) 1季度,
sum(case when to_char(cheindate,'dd')=4 then 1 else 0 end) 4月,
sum(case when to_char(cheindate,'dd')=5 then 1 else 0 end) 5月,
sum(case when to_char(cheindate,'dd')=6 then 1 else 0 end) 6月,
sum(case when 3<to_char(cheindate,'dd')<=6 then 1 else 0 end) 2季度,
sum(case when to_char(cheindate,'dd')=7 then 1 else 0 end) 7月,
sum(case when to_char(cheindate,'dd')=8 then 1 else 0 end) 8月,
sum(case when to_char(cheindate,'dd')=9 then 1 else 0 end) 9月,
sum(case when 6<to_char(cheindate,'dd')<=9 then 1 else 0 end) 3季度,
sum(case when to_char(cheindate,'dd')=10 then 1 else 0 end) 10月,
sum(case when to_char(cheindate,'dd')=11 then 1 else 0 end) 11月,
sum(case when to_char(cheindate,'dd')=12 then 1 else 0 end) 12月,
sum(case when 9<to_char(cheindate,'dd')<=12 then 1 else 0 end) 4季度,
count(*) 合计
from tablename
group by equipName