以下查詢在按預期回傳資料方面作業正常：
SELECT DISTINCT
CONVERT(varchar, b.bookindt, 101) AS [book-in date],
b.bookinno AS [book-in no.],
dbo.fn_getoffensedesc(o.offenseid, o.probviolation,
(select offense from trdcode61
where code61id = o.code61id), o.goc) AS offensedescription,
o.PrimaryOffense AS [Primary Offense],
trd.l_d AS [offense l/d],
p.firstname AS [first name],
p.lastname AS [last name]
FROM
tblpeople p
LEFT OUTER JOIN
tbloffense o (NOLOCK) ON o.personid = p.personid
LEFT OUTER JOIN
tblbookin b (NOLOCK) ON b.bookinid = o.bookinid
LEFT OUTER JOIN
trdcode61 trd (NOLOCK) ON trd.code61id = o.code61id
WHERE
dbo.fn_isinjailbybookinid(b.bookinid) = 1
-- AND b.bookinno='21042173'
AND (trd.l_d LIKE 'F%' OR trd.l_d LIKE 'M%')
ORDER BY
p.lastname, p.firstname
這是單個“bookinno”的結果截圖，更清晰：
我需要有關查詢的幫助，它會查看“預訂編號”。并且如果“Offense l/d”列包含一個，(trd.l_d like 'F%' AND trd.l_d like 'M%')則回傳“Book-in No.” 結果。
當我嘗試運行時，AND我得到的結果為零。
我確實有“預訂號”。只有“F%”或只有“M%”。
將不勝感激任何幫助。
問候，杰爾
uj5u.com熱心網友回復：
您可以使用條件過濾結果集，分組依據bookinno并在 HAVING 子句中設定條件，以便兩個條件都適用于該條件bookinno：
SELECT b.bookinno AS [book-in no.]
FROM tblpeople p
INNER JOIN tbloffense o ON o.personid = p.personid
INNER JOIN tblbookin b ON b.bookinid = o.bookinid
INNER JOIN trdcode61 trd ON trd.code61id = o.code61id
WHERE dbo.fn_isinjailbybookinid(b.bookinid) = 1
AND (trd.l_d LIKE 'F%' OR trd.l_d LIKE 'M%')
GROUP BY b.bookinno
HAVING COUNT(DISTINCT LEFT(trd.l_d, 1)) = 2;
請注意，此要求的聯接應該是，INNER而不是LEFT因為您不希望結果中出現任何不匹配的行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/365088.html