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