针对一些通用值分组的两个不同表的SQL查询

Question

我有两个表，一个用于学生/班级，另一个用于学生/兴趣，例如；

studentID   class
---------------------
4134              1
4135              1
4136              1
4137              1
4138              2
4139              2
4140              2
4141              2

studentID   interests
---------------------
4134        basketball
4134        football
4135        basketball
4136        basketball
4137        football
4138        swimming
4138        football
4139        running
4140        tennis
4141        tennis

询问哪些学生只与他的同学有相同的兴趣爱好，最好的方法是什么？最难的部分是只有的要求。

查询结果不应为4135、4136、4140和4141。由于4134和4137具有相同的兴趣，所以他们的兴趣都与4138相同，4138不在1类中。

Answer 1

我已经为你的数据创建了两个表-

CREATE TABLE #temp (id INT , class INT)
CREATE TABLE #temp2 (id INT, activity VARCHAR(500))

​

INSERT INTO #temp
SELECT 4134 ,             1   UNION ALL
SELECT 4135 ,             1   UNION ALL
SELECT 4136 ,             1   UNION ALL
SELECT 4137 ,             1   UNION ALL
SELECT 4138 ,             2   UNION ALL
SELECT 4139 ,             2   UNION ALL
SELECT 4140 ,             2   UNION ALL
SELECT 4141 ,             2   

INSERT INTO #temp2
SELECT 4134 ,      'basketball'        UNION ALL
SELECT 4134 ,      'football'        UNION ALL
SELECT 4135 ,      'basketball'        UNION ALL
SELECT 4136 ,      'basketball'        UNION ALL
SELECT 4137 ,      'football'        UNION ALL
SELECT 4138 ,      'swimming'        UNION ALL
SELECT 4138 ,      'football'        UNION ALL
SELECT 4139 ,      'running'        UNION ALL
SELECT 4140 ,      'tennis'        UNION ALL
SELECT 4141 ,      'tennis'


AND use select statement 

    SELECT DISTINCT t.id id
    FROM   #temp2 t
    INNER JOIN #temp2 t1 ON  t.activity = t1.activity AND t.id <> t1.id 
    INNER JOIN #temp t3 ON t.id = t3.id 
    INNER JOIN #temp t4 ON t1.id = t4.id
    WHERE t3.class = t4.class  AND t.id NOT IN (SELECT DISTINCT te.id id 
       FROM   #temp2 te
       INNER JOIN #temp2 te1 ON  te.activity = te1.activity AND te.id <> te1.id 
       INNER JOIN #temp te3 ON te.id = te3.id 
       INNER JOIN #temp te4 ON te1.id = te4.id
       WHERE te3.class <> te4.class)

This will return - 

id
4135
4136
4140
4141