英文:
MySQL - Select IDs occurring in any 2 or more of 3 tables
问题
我有3张表，每张表都包含ID。我想选择在这些表中出现在任何2张或更多张表中的所有ID。我该如何在MySQL 5.7中实现这个目标？
谢谢！
英文:
I have 3 tables, each containing IDs. I would like to select all IDs appearing in any 2 or more of these tables. How can I achieve this in MySQL 5.7?
Thanks!
答案1
得分: 2
使用联合方法和汇总，我们可以尝试：
SELECT ID
FROM
(
SELECT DISTINCT ID FROM Table1
UNION ALL
SELECT DISTINCT ID FROM Table2
UNION ALL
SELECT DISTINCT ID FROM Table3
) t
GROUP BY ID
HAVING COUNT(*) >= 2;
英文:
Using a union approach along with aggregation we can try:
<!-- language: sql -->
SELECT ID
FROM
(
SELECT DISTINCT ID FROM Table1
UNION ALL
SELECT DISTINCT ID FROM Table2
UNION ALL
SELECT DISTINCT ID FROM Table3
) t
GROUP BY ID
HAVING COUNT(*) >= 2;
答案2
得分: 0
WITH
t1 AS (SELECT DISTINCT id FROM table1),
t2 AS (SELECT DISTINCT id FROM table2),
t3 AS (SELECT DISTINCT id FROM table3),
comb AS (
SELECT id FROM t1
UNION ALL SELECT id FROM t2
UNION ALL SELECT id FROM t3
)
SELECT
id,
COUNT(*) AS occ_count
FROM comb
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY 1
英文:
WITH
t1 AS (SELECT DISTINCT id FROM table1)
, t2 AS (SELECT DISTINCT id FROM table2)
, t3 AS (SELECT DISTINCT id FROM table3)
, comb AS (
SELECT id FROM t1
UNION ALL SELECT id FROM t2
UNION ALL SELECT id FROM t3
)
SELECT
id
, COUNT(*) AS occ_count
FROM comb
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY 1
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论