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

我有以下表格:

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)

最佳答案

使用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)