我需要一个select查询,根据输入获取L_ID的相互R_ID
L_ID | R_ID|
-----|-----|
1 | 1 |
2 | 1 |
3 | 1 |
1 | 2 |
2 | 2 |
4 | 2 |
2 | 3 |
3 | 3 |
4 | 3 |
5 | 3 |
..
例如,如果我通过1,2,3:它返回1
SELECT R_ID FROM table WHERE L_ID in (1,2,3)
R_ID |
-----|
1 |
例如,如果我通过2,3,4,5:它返回3
SELECT R_ID FROM table WHERE L_ID in (4,2,3,5)
R_ID |
-----|
3 |
注:L_ID like(1,2,3)的所有组合对于一个公共的R_ID是唯一的
最佳答案
您可以使用group by
和having
。我想这就是你想要的:
SELECT R_ID
FROM table
WHERE L_ID in (1, 2, 3)
GROUP BY R_ID
HAVING COUNT(*) = 3; -- this value is the number of matches
注意
R_ID
与1、2、3和4匹配。如果这是不可取的,那么:SELECT R_ID
FROM table
GROUP BY R_ID
HAVING SUM(L_ID IN (1, 2, 3)) = 3 AND
COUNT(*) = 3;
关于mysql - 在不相交SQL查询的情况下获取相互ID,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48541434/