如何在MySQL/Oracle中统计指定ID在多表中的出现次数

刚好碰到过类似需求，给你整理了MySQL和Oracle通用的查询语句，直接就能用：

基础版查询语句（仅返回存在匹配的表） #

SELECT 'A' AS `Table name`, COUNT(*) AS `No of occurence` FROM A WHERE ID = 4
UNION ALL
SELECT 'B' AS `Table name`, COUNT(*) AS `No of occurence` FROM B WHERE ID = 4
UNION ALL
SELECT 'C' AS `Table name`, COUNT(*) AS `No of occurence` FROM C WHERE ID = 4
UNION ALL
SELECT 'D' AS `Table name`, COUNT(*) AS `No of occurence` FROM D WHERE ID = 4;

进阶版查询语句（所有表都显示，无匹配则返回0） #

如果希望不管表中有没有ID=4的记录，都能在结果里看到表名并显示0，可以用左连接的方式：

SELECT t.table_name, COALESCE(c.cnt, 0) AS `No of occurence`
FROM (
    SELECT 'A' AS table_name UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D'
) t
LEFT JOIN (
    SELECT 'A' AS table_name, COUNT(*) AS cnt FROM A WHERE ID=4
    UNION ALL
    SELECT 'B', COUNT(*) FROM B WHERE ID=4
    UNION ALL
    SELECT 'C', COUNT(*) FROM C WHERE ID=4
    UNION ALL
    SELECT 'D', COUNT(*) FROM D WHERE ID=4
) c ON t.table_name = c.table_name;

关键说明 #

• 用UNION ALL替代UNION，避免不必要的去重操作，提升查询效率
• COALESCE函数用来处理空值，把无匹配时的NULL转换成0
• 字段名用反引号（MySQL）或双引号（Oracle）包裹，避免和关键字冲突

示例输出（对应你的需求） #

内容的提问来源于stack exchange，提问作者sravan kumar