3,491
社区成员
发帖
与我相关
我的任务
分享
P_TEST(2013/5/5,2013/8/12)
KH SP 05 06 07 08
------------------------------------------------------------
1 1号店 苹果4 0 330 200 0
2 1号店 苹果4s 0 0 300 0
3 2号店 苹果4s 440 0 0 0
4 2号店 苹果5 0 0 0 100
5 3号店 苹果4 0 0 0 250
6 3号店 苹果5 0 0 0 350
CREATE OR REPLACE PROCEDURE P_TEST (B_DATE IN DATE,E_DATE IN DATE)
IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT TO_CHAR(T.SJ,'MM') SJ
FROM tab1 T
WHERE T.SJ BETWEEN B_DATE AND E_DATE
ORDER BY TO_CHAR(T.SJ,'MM');
BEGIN
V_SQL := 'SELECT KH,SP ';
FOR V_TB IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(TO_CHAR(T.SJ,''MM''),'''
|| V_TB.SJ || ''',SL,0)) AS "'|| V_TB.SJ ||'"';
END LOOP;
V_SQL := V_SQL || ' FROM TAB1 T' ;
V_SQL := V_SQL || ' WHERE T.SJ BETWEEN '||B_DATE|| ' AND '||E_DATE;
V_SQL := V_SQL || ' GROUP BY KH,SP ORDER BY KH,SP';
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;
create table tab1 (kh nvarchar2(20),sp nvarchar2(20),sl number(6),sj date);
insert into tab1
select '1号店','苹果4',200,date'2013-07-05' from dual union all
select '2号店','苹果5',100,date'2013-08-05' from dual union all
select '1号店','苹果4s',300,date'2013-07-13' from dual union all
select '3号店','苹果5',350,date'2013-08-08' from dual union all
select '2号店','苹果5s',600,date'2013-08-15' from dual union all
select '4号店','苹果4',150,date'2013-09-05' from dual union all
select '1号店','苹果4',330,date'2013-06-05' from dual union all
select '2号店','苹果4s',440,date'2013-05-15' from dual union all
select '3号店','苹果4',250,date'2013-08-11' from dual
commit;
drop table tab1;
SELECT A.商品,A.客户,
SUM(DECODE(R.MONTH2,'2013-05',A.数量,0)) "5月数量",
SUM(DECODE(R.MONTH2,'2013-06',A.数量,0)) "6月数量",
SUM(DECODE(R.MONTH2,'2013-07',A.数量,0)) "7月数量",
SUM(DECODE(R.MONTH2,'2013-08',A.数量,0)) "8月数量",
SUM(DECODE(R.MONTH2,'2013-09',A.数量,0)) "9月数量"
FROM(
SELECT T.商品,T.客户,T.数量, SUBSTR(T.时间,1,7)MONTH2
FROM TAB1 T WHERE T.商品='苹果4'
AND T.时间>= '2013-05-16' AND T.时间<='2013-09-17'
GROUP BY T.商品,T.客户,T.数量,SUBSTR(T.时间,1,7)
)A
RIGHT JOIN
(
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(
SUBSTR('2013-05-16',1,7), 'YYYY-MM'), ROWNUM - 1),
'YYYY-MM') AS MONTH2 FROM DUAL
CONNECT BY ROWNUM <= (SELECT MONTHS_BETWEEN(TO_DATE(
SUBSTR('2013-09-17',1,7), 'YYYY-MM'),
TO_DATE(SUBSTR('2013-05-16',1,7), 'YYYY-MM'))FROM DUAL)+1
)R ON A.MONTH2 = R.MONTH2
WHERE A.商品||A.客户 IS NOT NULL
GROUP BY A.商品,A.客户;
商品 客户 5月数量 6月数量 7月数量 8月数量 9月数量
--------- --------------- ---------- ---------- ---------- ----------
苹果4 3号店 0 0 0 450 0
苹果4 1号店 0 0 600 0 0
表结构:
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
编号 VARCHAR2(50) Y
客户 VARCHAR2(50) Y
业务员 VARCHAR2(50) Y
商品 VARCHAR2(50) Y
单价 VARCHAR2(50) Y
数量 VARCHAR2(50) Y
时间 VARCHAR2(50) Y
测试数据:
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO001', '1号店', '小明', '苹果4', '3000', '200', '2013-07-05');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO002', '2号店', '小明', '苹果5', '4200', '100', '2013-08-05');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO003', '1号店', '小明', '苹果4s', '3500', '300', '2013-07-13');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO004', '3号店', '小明', '苹果4', '3000', '350', '2013-08-09');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO005', '2号店', '小明', '苹果5', '4100', '600', '2013-08-18');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO006', '4号店', '小明', '苹果5s', '4500', '150', '2013-09-07');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO001', '3号店', '小明', '苹果4', '3100', '100', '2013-08-23');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO001', '4号店', '小明', '苹果4s', '3600', '700', '2013-09-16');
insert into tab1 (编号, 客户, 业务员, 商品, 单价, 数量, 时间)
values ('SO001', '1号店', '小明', '苹果4', '3200', '400', '2013-07-22');