1。不正确的使用联机视图; 2。在不需要排序的地方进行排序; 3。子查询嵌套太多; 4。not in ('零售'),效率也较低; 5。如果逻辑实在太复杂,建议建立存储过程解决。
改进了的SQL语句,原语句查询需要用30秒,现在的只需要用20秒了SELECT LS.C_TSID,LS.LSJZ,0 AS PFJZ FROM (SELECT C_TSID,SUM(N_CS) AS LSJZ FROM( SELECT C_TSID,N_CS FROM T_XSD,T_XSM WHERE T_XSD.C_XSID=T_XSM.C_XSID AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX IN('零售') AND SUBSTR(C_TSID,1,1)='B' UNION SELECT C_TSID,-N_CS FROM T_XSTHD,T_XSTHM WHERE T_XSTHD.C_XSID=T_XSTHM.C_XSID AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX IN('零售') AND SUBSTR(C_TSID,1,1)='B')GROUP BY C_TSID)LS UNION SELECT PF.C_TSID,0 AS LSJZ,PF.PFJZ FROM (SELECT C_TSID,SUM(N_CS) AS PFJZ FROM( SELECT C_TSID,N_CS FROM T_XSD,T_XSM WHERE T_XSD.C_XSID=T_XSM.C_XSID AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX NOT IN('零售') AND SUBSTR(C_TSID,1,1)='B' UNION SELECT C_TSID,-N_CS FROM T_XSTHD,T_XSTHM WHERE T_XSTHD.C_XSID=T_XSTHM.C_XSID AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX NOT IN('零售') AND SUBSTR(C_TSID,1,1)='B')GROUP BY C_TSID)PF
2。在不需要排序的地方进行排序;
3。子查询嵌套太多;
4。not in ('零售'),效率也较低;
5。如果逻辑实在太复杂,建议建立存储过程解决。
(SELECT C_TSID,SUM(N_CS) AS LSJZ FROM(
SELECT C_TSID,N_CS FROM T_XSD,T_XSM WHERE T_XSD.C_XSID=T_XSM.C_XSID
AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX IN('零售')
AND SUBSTR(C_TSID,1,1)='B'
UNION
SELECT C_TSID,-N_CS FROM T_XSTHD,T_XSTHM WHERE T_XSTHD.C_XSID=T_XSTHM.C_XSID
AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX IN('零售')
AND SUBSTR(C_TSID,1,1)='B')GROUP BY C_TSID)LS
UNION SELECT PF.C_TSID,0 AS LSJZ,PF.PFJZ FROM
(SELECT C_TSID,SUM(N_CS) AS PFJZ FROM(
SELECT C_TSID,N_CS FROM T_XSD,T_XSM WHERE T_XSD.C_XSID=T_XSM.C_XSID
AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX NOT IN('零售')
AND SUBSTR(C_TSID,1,1)='B'
UNION
SELECT C_TSID,-N_CS FROM T_XSTHD,T_XSTHM WHERE T_XSTHD.C_XSID=T_XSTHM.C_XSID
AND C_FDID IN ('002') AND D_XSRQ = '2003-01-27' AND V_XSLX NOT IN('零售')
AND SUBSTR(C_TSID,1,1)='B')GROUP BY C_TSID)PF
我一直以为我的sql是很复杂的了
今日方悟天外有天人外有人里边每个row里的相关field中都进行了嵌套处理
这都是以row为依据进行计算
这样下来每取出一行都要处理相关内部的嵌套查询
其实这些都是进行了重复计算
建议还是少写嵌套语句