例如:
tab1(销售订单表):
编号 客户 业务员 商品 单价 数量 时间
SO001 1号店 小明 苹果4 3000 200 2013-07-05
SO002 2号店 小明 苹果5 4200 100 2013-08-05
SO003 1号店 小明 苹果4S 3500 300 2013-07-13
SO004 3号店 小明 苹果4 3000 350 2013-08-09
SO005 2号店 小明 苹果5 4100 600 2013-08-18
SO006 4号店 小明 苹果5S 4500 150 2013-09-07
SO001 3号店 小明 苹果4 3100 100 2013-08-23
SO001 4号店 小明 苹果4S 3600 700 2013-09-16
SO001 1号店 小明 苹果4 3200 400 2013-07-22
希望统计得到苹果4的销售数量:
商品 客户 7月数量 8月数量 9月数量
苹果4 1号店 600 0 0
苹果4 3号店 0 450 0PS:现在有2个参数日期,一个开始日期和结束日期,根据日期来决定查询的月份。
如参数开始日期:2013-05-16,结束日期:2013-09-17
那么我查询到的月份字段就有5月数量、6月数量、7月数量、8月数量、9月数量,而5月份的数据必须是5月16号之后的,同样,9月统计的数量必须是9月17号之前的。求语句,我现在的做法是根据2个日期得到月份,然后循环月份,把月份放进SQL语句执行,每个月每个月的查,如果我2个日期区间有10个月,那么我一条数据就要访问10次数据库,导致查询效率大大降低。查询到的数据还没100条,却要花上将近1分钟的时间。像这种直接统计的不会啊,求大神
tab1(销售订单表):
编号 客户 业务员 商品 单价 数量 时间
SO001 1号店 小明 苹果4 3000 200 2013-07-05
SO002 2号店 小明 苹果5 4200 100 2013-08-05
SO003 1号店 小明 苹果4S 3500 300 2013-07-13
SO004 3号店 小明 苹果4 3000 350 2013-08-09
SO005 2号店 小明 苹果5 4100 600 2013-08-18
SO006 4号店 小明 苹果5S 4500 150 2013-09-07
SO001 3号店 小明 苹果4 3100 100 2013-08-23
SO001 4号店 小明 苹果4S 3600 700 2013-09-16
SO001 1号店 小明 苹果4 3200 400 2013-07-22
希望统计得到苹果4的销售数量:
商品 客户 7月数量 8月数量 9月数量
苹果4 1号店 600 0 0
苹果4 3号店 0 450 0PS:现在有2个参数日期,一个开始日期和结束日期,根据日期来决定查询的月份。
如参数开始日期:2013-05-16,结束日期:2013-09-17
那么我查询到的月份字段就有5月数量、6月数量、7月数量、8月数量、9月数量,而5月份的数据必须是5月16号之后的,同样,9月统计的数量必须是9月17号之前的。求语句,我现在的做法是根据2个日期得到月份,然后循环月份,把月份放进SQL语句执行,每个月每个月的查,如果我2个日期区间有10个月,那么我一条数据就要访问10次数据库,导致查询效率大大降低。查询到的数据还没100条,却要花上将近1分钟的时间。像这种直接统计的不会啊,求大神
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;
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;
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
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月数量"
只需要替换上面的这些,其余的可以参考下面的这段: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');
你们的处理方法差不多,刚刚代码修改了,测试OK,原先查询2个月的数据大概有七八十条的样子,要12秒左右,现在只要6秒的样子(因为查询的内容过多,而且查出来的数据不是直接绑定,而是要在页面画HTML,数据显示要这么久),原先查询3个月的数据700条的样子,不知道要多久,现在也要30秒。我没有用到存储过程,直接页面写的SQL语句,虽然感觉还是有点慢,但现在也没时间继续优化了
string sql = "SELECT KH,SP";
for(int i = 0; i< 集合.length;i++)
{
sql += ",SUM(DECODE(TO_CHAR(T.SJ,'MM'),'"+集合(i)+"',SL,0) ";
}
sql += " where .. group by ...";