我有下面这张桌子 成员:
| ID | hobby |
|---|---|
| 1 | Football |
| 1 | Tennis |
| 1 | Football |
| 2 | Cards |
| 2 | Painting |
| 3 | Tennis |
| 3 | Football |
| 4 | Cards |
我只想 Select 成对的成员，如果他们有完全相同的爱好(没有重复). 因此，在上表中，我希望该查询输出:
| id1 | id2 |
|---|---|
| 1 | 3 |
我的疑问是:
SELECT m1.id as id1 , m2.id as id2
FROM members m1 inner join members m2
ON m1.id < m2.id
WHERE m1.hobby in (
SELECT distinct(m2.hobby)
)
GROUP BY id1,id2
但我得到了:
| id1 | id2 |
|---|---|
| 1 | 3 |
| 2 | 4 |
实现这一点的一种方法是:
WITH cte AS (
SELECT ID,
hobby,
COUNT(hobby) OVER(PARTITION BY ID) AS cnt
FROM tab
GROUP BY ID,
hobby
)
SELECT t1.ID AS id1,
t2.ID AS id2
FROM cte t1
INNER JOIN cte t2
ON t1.ID < t2.ID
AND t1.hobby = t2.hobby
AND t1.cnt = t2.cnt
GROUP BY t1.ID, t2.ID, t1.cnt
HAVING COUNT(*) = t1.cnt
Output:
| id1 | id2 |
|---|---|
| 1 | 3 |
查看演示here.