- SQL code
select * from ( SELECT ROW_NUMBER() OVER(ORDER BY AGT_CODE DESC) AS REC_NUM,* FROM ( SELECT INT_ORG, AGT_CODE, AGT_STATUS, '1' AS CLS FROM AGENT WHERE AGT_CODE NOT IN (SELECT OPEN_AGENT FROM CUSTOMER) AND AGT_CODE NOT IN (SELECT USER_CODE FROM AGENT_FUNDBIZ) AND AGT_STATUS = '0' UNION ALL SELECT A.INT_ORG, A.AGT_CODE, A.AGT_STATUS, '2' AS CLS FROM AGENT A, AGENT_RELATION C WHERE A.AGT_CODE = C.USER_CODE AND A.AGT_CODE NOT IN (SELECT OPEN_AGENT FROM CUSTOMER) AND A.AGT_STATUS = '9' ) D ) E WHERE E.REC_NUM BETWEEN 1 AND 900
我测试了下，主要是加了 WHERE E.REC_NUM BETWEEN 1 AND 900 后就变的很慢，如果不加的话是没有什么影响的。
------解决方案--------------------
- SQL code
SELECT *FROM (SELECT Row_number() OVER( ORDER BY AGT_CODE DESC) AS REC_NUM, * FROM (SELECT INT_ORG, AGT_CODE, AGT_STATUS, '1' AS CLS FROM AGENT B WHERE NOT EXISTS (SELECT OPEN_AGENT FROM CUSTOMER WHERE OPEN_AGENT=B.AGT_CODE) AND NOT EXISTS (SELECT USER_CODE FROM AGENT_FUNDBIZ WHERE USER_CODE=B.AGT_CODE) AND AGT_STATUS = '0' UNION ALL SELECT A.INT_ORG, A.AGT_CODE, A.AGT_STATUS, '2' AS CLS FROM AGENT A, AGENT_RELATION C WHERE A.AGT_CODE = C.USER_CODE AND NOT EXISTS (SELECT OPEN_AGENT FROM CUSTOMER WHERE OPEN_AGENT=A.AGT_CODE) AND A.AGT_STATUS = '9') D) EWHERE E.REC_NUM>= 1 AND E.REC_NUM<=900--- WHERE E.REC_NUM BETWEEN 1 AND 900 查看执行计划其实优化器最后实际将其转换为了 >= and <= 这种方式。--NOT IN 不会执行索引查找 而 NOT EXISTS 是会执行索引查找 操作的
------解决方案--------------------
试试这样如何？
WITH t AS (SELECT a.Int_Org, a.Agt_Code, a.Agt_Status, '1' AS Cls
FROM AGENT a
WHERE NOT EXISTS (SELECT 1
FROM Customer b
WHERE b.Open_Agent = a.Agt_Code)
AND NOT EXISTS (SELECT 1
FROM Agent_Fundbiz c
WHERE c.User_Code = a.Agt_Code)
AND a.Agt_Status = '0'
UNION ALL
SELECT a.Int_Org, a.Agt_Code, a.Agt_Status, '2' AS Cls
FROM AGENT a, Agent_Relation c
WHERE a.Agt_Code = c.User_Code
AND NOT EXISTS (SELECT 1
FROM Customer c
WHERE c.Open_Agent = a.Agt_Code)
AND a.Agt_Status = '9')
SELECT *
FROM (SELECT Row_Number() Over(ORDER BY Agt_Code DESC) AS Rec_Num,*
FROM t d) e
WHERE e.Rec_Num >= 1
AND e.Rec_Num < 901
------解决方案--------------------
分页
------解决方案--------------------
- SQL code
SELECT *FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY AGT_CODE DESC ) AS REC_NUM , * FROM ( SELECT INT_ORG , AGT_CODE , AGT_STATUS , '1' AS CLS FROM AGENT WHERE NOT EXISTS ( SELECT 1 FROM CUSTOMER WHERE agent.AGT_CODE = OPEN_AGENT ) AND NOT EXISTS ( SELECT 1 FROM AGENT_FUNDBIZ WHERE agent.AGT_CODE = USER_CODE ) AND AGT_STATUS = '0' UNION ALL SELECT A.INT_ORG , A.AGT_CODE , A.AGT_STATUS , '2' AS CLS FROM AGENT A , AGENT_RELATION C WHERE A.AGT_CODE = C.USER_CODE AND NOT EXISTS ( SELECT 1 FROM CUSTOMER WHERE agent.AGT_CODE = OPEN_AGENT ) AND A.AGT_STATUS = '9' ) D ) EWHERE E.REC_NUM BETWEEN 1 AND 900