如何从第二个表中选择所有id都存在于第一个表中的id

Question

我有以下表格:

TravelPage

TravelPageID     TravelIDRef     PassengerIDRef
    1                1                10
    2                1                15
    3                2                10

临时

PassengerID
   10
   15

我想从TravelPage表中得到所有travelIDRefs, temp table表中所有PassegerIDs(10,15)都存在于travelPage table中

结果应为

travelIDRef=1因为10和15只存在于TravelIDRef=1

declare @TravelPage table
(
   TravelPageID int,
   TravelIdRef int,
   PassengerIDRef int
)
declare @temp table
(
   PassengerID int
)

insert into @TravelPage
  values(1,1,10),(2,1,15),(3,2,10)
insert into @temp
values(10),(15)

Answer 1

使用HAVING子句:

SELECT t.TravelIdRef
FROM TravelPage t
CROSS JOIN Temp p
GROUP BY TravelIdRef
HAVING COUNT(DISTINCT t.PassengerIDRef) = COUNT(DISTINCT p.PassengerID)

或者使用子查询代替join:

SELECT t.TravelIdRef
FROM TravelPage t
GROUP BY TravelIdRef
HAVING COUNT(DISTINCT t.PassengerIDRef) = 
       (SELECT COUNT(DISTINCT p.PassengerID) FROM Temp p)