日期:2014-05-18  浏览次数:20642 次

如内的一条SQL语句的Where部分如何修改以提高查询效率
SELECT   DISTINCT   C.MG_TYPE   AS   类别,   A.CODENO   AS   代号,C.NAME   AS   名称   ,C.MG_GUIGE   AS   规格,A.TNUM   AS   数量,C.REMARK   AS   备注
FROM   @TEMPBOM   A
JOIN   DBOM   B   ON   B.ID   =   A.ID
JOIN   PAPITEM   C   ON   C.ID   =   A.ITEMID     AND   C.MTYPE   = '外购件 '
WHERE   A.ITEMID   NOT   IN
(SELECT   DISTINCT   ITEMID   FROM   DBOM   F
JOIN   PAPITEM   G   ON   G.MTYPE= '外购件 '   AND   F.ITEMID=G.ID
WHERE   F.BOMSTR   NOT   IN   (SELECT   BOMSTR   FROM   @TEMPBOM))
ORDER   BY   A.CODENO

------解决方案--------------------
SELECT DISTINCT C.MG_TYPE AS 类别, A.CODENO AS 代号,C.NAME AS 名称 ,C.MG_GUIGE AS 规格,A.TNUM AS 数量,C.REMARK AS 备注
FROM @TEMPBOM A
JOIN DBOM B ON B.ID = A.ID
JOIN PAPITEM C ON C.ID = A.ITEMID AND C.MTYPE = '外购件 '
WHERE A.ITEMID NOT IN
(SELECT DISTINCT ITEMID FROM (select * from DBOM WHERE BOMSTR NOT IN (SELECT BOMSTR FROM @TEMPBOM)) F
JOIN (select * from PAPITEM where MTYPE= '外购件 ') G
ON F.ITEMID=G.ID

ORDER BY A.CODENO
------解决方案--------------------
在企业管理器—选项—高级—设置登陆时间(默认是4秒或15秒),问一下楼主用的什么版本?如时2000有没有装SP4
------解决方案--------------------
SELECT DISTINCT
C.MG_TYPE AS 类别,
A.CODENO AS 代号,
C.NAME AS 名称 ,
C.MG_GUIGE AS 规格,
A.TNUM AS 数量,
C.REMARK AS 备注
FROM @TEMPBOM A JOIN DBOM B ON B.ID = A.ID
JOIN PAPITEM C ON C.ID = A.ITEMID AND C.MTYPE = '外购件 '
WHERE NOT EXISTS(SELECT *
FROM DBOM F JOIN PAPITEM G ON G.MTYPE= '外购件 ' AND F.ITEMID=G.ID
WHERE NOT EXISTS (SELECT BOMSTR FROM @TEMPBOM WHERE BOMSTR = F.BOMSTR)
AND F.ITEMID = A.ITEMID)
ORDER BY A.CODENO
最好用执行计划分析一下,找出问题!
------解决方案--------------------

SELECT DISTINCT C.MG_TYPE AS 类别, A.CODENO AS 代号,C.NAME AS 名称 ,C.MG_GUIGE AS 规格,A.TNUM AS 数量,C.REMARK AS 备注
FROM @TEMPBOM A
JOIN DBOM B ON B.ID = A.ID
JOIN PAPITEM C ON C.ID = A.ITEMID AND C.MTYPE = '外购件 '
where not exists(
select 1 from
( SELECT ITEMID
FROM DBOM F
JOIN PAPITEM G ON G.MTYPE= '外购件 ' AND F.ITEMID=G.ID
where not exists(select 1 from @TEMPBOM where F.BOMSTR=BOMSTR)) T
where T.ITEMID =A.ITEMID
)
ORDER BY A.CODENO