[T-SQL] INTERSECT 交集查詢

最近寫程式遇到的一個問題需要使用到SQL的交集查詢的功能，問題內容如下，我有一群各式各樣的"藥"的資料，而每一個藥是由"不同成分"所組成，我需要知道的是同時符合某些成分的要有哪些?

這個問題敘述乍看之下很簡單(符合某些成分的藥)，但仔細想才覺得事情不是傻人的想的那麼簡單，問題的重點在於需要同時符合某幾個成分，這樣的藥有哪些，以下面的例子來看

SELECT [MedID]
      ,[MedName]
  FROM [Northwnd].[dbo].[MedTB]

SELECT [MedID]
      ,[SubMedName]
  FROM [Northwnd].[dbo].[SubMedTB]

SELECT     MedTB.MedName,SubMedTB.SubMedName
FROM         SubMedTB INNER JOIN
                      MedTB ON SubMedTB.MedID = MedTB.MedID

 

強力丸的成分有維他命A及維他命B，還魂丹有維他命A及維他命C，而十香軟筋散則是有維他命A、維他命B及維他命C，如果我想要找有維他命A的藥，則三種藥名都符合，但如果要同時有維他命A及維他命B的則有強力丸及十香軟筋散，同理有維他命A及維他命C的有環魂丹及十香軟筋散，但同時有維他命ABC的則只剩下十香軟筋散。這樣子的SQL查詢要怎麼下呢?我首先想到使用T-SQL中 INTERSECT的語法，假設我要找維他命A及維他命C的，則語法如下

SELECT     MedTB.MedName
FROM         SubMedTB INNER JOIN
                      MedTB ON SubMedTB.MedID = MedTB.MedID
WHERE SubMedTB.SubMedName = N'維他命A'

INTERSECT 

SELECT     MedTB.MedName
FROM         SubMedTB INNER JOIN
                      MedTB ON SubMedTB.MedID = MedTB.MedID
WHERE SubMedTB.SubMedName = N'維他命B'

查詢到的結果如下

相同的道理，需要幾種成分同時符合的話，就繼續以SELECT組合INTERSECT與法即可達成，但是這樣的撰法方法，卻對於前端的程式呼叫造成極大的困擾，如果我的程式是規定不能組合SQL字串的話，那便不能用這個方法來解決(當然你可以把它寫進StoredProcedure or SQL Function也可，但一樣都需要判斷進來的成分參數來組合SQL)，我比較prefer的是前端程式可以直接丟參數給既定的SQL即可，這樣才是我心目中優良的程式撰寫方式，最後請教 pinnywu大神後，給出這樣的SQL

SELECT MedName
FROM
(
SELECT     MedTB.MedName,COUNT(1) as cnt
FROM         SubMedTB INNER JOIN
                      MedTB ON SubMedTB.MedID = MedTB.MedID
WHERE SubMedTB.SubMedName IN( N'維他命B' , N'維他命A') --參數內容 這裡可以直接丟參數
GROUP BY MedTB.MedName
) AS TA
WHERE cnt = 2 -- IN中參數數量 這裡可以直接丟參數

這段SQL非常好玩，有興趣的可以先從sub select開始解讀，但我這裡要強調的是，這樣子的SQL才能避掉組SQL的方式，我只需要於前端程式中，於我註解的地方改成丟參數的方式，及可以把對應的參數傳入，例如如果我要找同時符合維他命ABC的，則會在IN的參數地方丟入維他命ABC的查詢結果，然後於cnt的地方丟恕我總共的參數數目是3，這個時候就可以查詢到十香軟筋散了，不知道各位看官懂不懂我再說什麼呢?

有興趣的大家可以討論一下。

註:其實如果都要組SQL了，那除了INTERSECT這種罕用關鍵字外，還有其他的方式可以解決這個問題。下次有機會再跟大家分享啦