我想讓我的查詢更好。我有什么機會更改此代碼以獲得更快的代碼？
SELECT sn
FROM package p
WHERE StatusID = 1
AND ( NOT EXISTS( SELECT * FROM packagedetail pd
WHERE pd.packageID = p.ID AND packageDetailStatus = 12 )
OR ( EXISTS ( SELECT * FROM packagedetail pd
WHERE pd.packageID = p.ID AND packageDetailStatus = 12)
AND EXISTS ( SELECT * FROM unit u JOIN unitDetail ON u.ID = ud.unitID
WHERE (ud.InmostPackageID = p.ID OR ud.OutmostPackageID = p.ID)
AND u.UnitStateID in (8120, 8130, 8140)
)
)
)
（我知道這很丑，但我想知道，我怎樣才能提高我的技能）
uj5u.com熱心網友回復：
您可以做的一種簡化是不再重復NOT EXISTS子查詢。
以下查詢在語意上與您的查詢等效：
SELECT p.sn
FROM package p
WHERE p.StatusID = 1
AND (
NOT EXISTS(SELECT * FROM packagedetail pd
WHERE pd.packageID = p.ID AND packageDetailStatus = 12)
OR
EXISTS (SELECT * FROM unit u JOIN unitDetail ON u.ID = ud.unitID
WHERE (ud.InmostPackageID = p.ID OR ud.OutmostPackageID = p.ID) AND u.UnitStateID in (8120, 8130, 8140))
);
因為：
(NOT X) OR (X AND Y) = (NOT X) OR Y
uj5u.com熱心網友回復：
一種可能使其更易于閱讀的替代方法：
SELECT sn
FROM
package p
cross apply
(select
exists_packagedetail =case when exists (
SELECT *
FROM packagedetail pd
WHERE pd.packageID = p.ID AND packageDetailStatus = 12
) then 1 else 0 end
,exists_unitdetail =case when exists (
SELECT *
FROM
unit u
JOIN unitDetail ON u.ID = ud.unitID
WHERE (ud.InmostPackageID = p.ID OR ud.OutmostPackageID = p.ID) AND u.UnitStateID in (8120, 8130, 8140)
)then 1 else 0 end
) as q1
WHERE StatusID = 1 and (q1.exists_unitdetail=1 or q1.exists_packagedetail=0)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/495819.html
上一篇：SQL-顯示2列