这个IN条件如何转换为EXISTS条件
SELECT *
FROM dbo.Inquiry
WHERE IQ_RowID IN
(
SELECT IQ_RowID
FROM dbo.Inquiry
WHERE PJ_RowID = @ProjectGuid
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.SampleRevision sr
ON sr.PJ_RowID = p.PJ_RowID
INNER JOIN dbo.Inquiry iq
ON iq.InquiryID = sr.InquiryID
WHERE p.PJ_RowID = @ProjectGuid
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.InquiryPart ip
ON ip.PartID = p.SampleID
INNER JOIN dbo.Inquiry iq
ON iq.IQ_RowID = ip.IQ_RowID
WHERE p.PJ_RowID = @ProjectGuid
)
ORDER BY AddDate
转成EXISTS 条件如何实现?
[解决办法]
SELECT *
FROM dbo.Inquiry
WHERE EXISTS
(select 1 from(
SELECT IQ_RowID
FROM dbo.Inquiry
WHERE PJ_RowID = @ProjectGuid
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.SampleRevision sr
ON sr.PJ_RowID = p.PJ_RowID
INNER JOIN dbo.Inquiry iq
ON iq.InquiryID = sr.InquiryID
WHERE p.PJ_RowID = @ProjectGuid
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.InquiryPart ip
ON ip.PartID = p.SampleID
INNER JOIN dbo.Inquiry iq
ON iq.IQ_RowID = ip.IQ_RowID
WHERE p.PJ_RowID = @ProjectGuid)_x
where _x.IQ_RowID = IQ_RowID
)
ORDER BY AddDate
[解决办法]
SELECT *
FROM dbo.Inquiry as a
WHERE exists
(
SELECT IQ_RowID
FROM dbo.Inquiry
WHERE PJ_RowID = @ProjectGuid and IQ_RowID = a.IQ_RowID
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.SampleRevision sr
ON sr.PJ_RowID = p.PJ_RowID
INNER JOIN dbo.Inquiry iq
ON iq.InquiryID = sr.InquiryID
WHERE p.PJ_RowID = @ProjectGuid and IQ_RowID = a.IQ_RowID
UNION
SELECT iq.IQ_RowID
FROM dbo.project p
INNER JOIN dbo.InquiryPart ip
ON ip.PartID = p.SampleID
INNER JOIN dbo.Inquiry iq
ON iq.IQ_RowID = ip.IQ_RowID
WHERE p.PJ_RowID = @ProjectGuid and IQ_RowID = a.IQ_RowID
)
ORDER BY AddDate
[解决办法]
SELECT *
FROM dbo.Inquiry A
WHERE
EXISTS(SELECT 1
FROM dbo.Inquiry
WHERE PJ_RowID = @ProjectGuid AND IQ_RowID=A.IQ_RowID)
OR EXISTS(SELECT 1
FROM dbo.project p
INNER JOIN dbo.SampleRevision sr
ON sr.PJ_RowID = p.PJ_RowID
INNER JOIN dbo.Inquiry iq
ON iq.InquiryID = sr.InquiryID
WHERE p.PJ_RowID = @ProjectGuid AND iq.IQ_RowID=A.IQ_RowID)
OR EXISTS(SELECT 1
FROM dbo.project p
INNER JOIN dbo.InquiryPart ip
ON ip.PartID = p.SampleID
INNER JOIN dbo.Inquiry iq
ON iq.IQ_RowID = ip.IQ_RowID
WHERE p.PJ_RowID = @ProjectGuid AND iq.IQ_RowID=A.IQ_RowID)
ORDER BY AddDate
