sql-server - 如何从第二个表中选择所有ID在第一个表中存在的ID？

Question

我有以下表格:

TravelPage

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

温度

PassengerID
   10
   15

我想从 travelIDRefs表中获取所有 TravelPage，而 PassegerIDs(10,15)中存在所有 temp table中的 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)

或使用子查询代替联接:

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