如何找到与SQL中的另一个表相匹配的表的一组行?

I have two tables in SQL Server,

SQL Server中有两个表，

Declare @Table1 Table ( TID1 INT, TP1 INT)

Declare @Table2 Table ( TID2 INT, TP2 INT)


INSERT INTO @Table1 (TID1,TP1) VALUES (100,1)
INSERT INTO @Table1 (TID1,TP1) VALUES (100,2)
INSERT INTO @Table1 (TID1,TP1) VALUES (100,3)

INSERT INTO @Table2 (TID2,TP2) VALUES (101,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (101,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (101,3)
INSERT INTO @Table2 (TID2,TP2) VALUES (102,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (102,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,1)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,3)
INSERT INTO @Table2 (TID2,TP2) VALUES (103,4)
INSERT INTO @Table2 (TID2,TP2) VALUES (104,2)
INSERT INTO @Table2 (TID2,TP2) VALUES (105,3)

Having Data as :

有数据:

TID1        TP1
----------- -----------
100         1
100         2
100         3


TID2        TP2
----------- -----------
101         1
101         2
101         3
102         1
102         2
103         1
103         2
103         3
103         4
104         2
105         3

I want to select those records which having exact matching of TP1 column in Table2 TP2 column. EX TID2 having ID 101 will be only in result set

我想选择表2 TP2列中与TP1列完全匹配的记录。有ID 101的EX TID2将只在结果集中

3 个解决方案

#1

SELECT t2.TID2
FROM @Table2 t2
LEFT JOIN @Table1 t1
    ON t2.TP2 = t1.TP1
GROUP BY t2.TID2
HAVING SUM(CASE WHEN t1.TP1 IS NULL THEN 1 ELSE 0 END) = 0 AND
       COUNT(*) = (SELECT COUNT(*) FROM @Table1)

#2

Try Like below.

试着像下面。

SELECT TID2
FROM   @TABLE1 T
       RIGHT JOIN  @TABLE2 T2
         ON T.TP1 = T2.TP2
GROUP  BY TID2
HAVING COUNT(T2.TP2) = (SELECT COUNT(*) FROM @TABLE1)

#3

-- you can calculated this in CTEW or sub-query if you do not like to be in variable
DECLARE @MaxRowsCount INT = (SELECT COUNT(*) FROM @Table1);

SELECT T2.[TID2]
FROM @Table2 T2
LEFT JOIN @Table1 T1
    ON T2.[TP2] = T1.[TP1]
GROUP BY T2.[TID2]
HAVING
(
    -- current count of rows should be the same as the row count from the first table
    COUNT(T2.[TP2]) = @MaxRowsCount
)