MySQL中统计特定行的多表引用行数及无外键关联场景处理

这个问题我之前帮不少开发者处理过，分两种核心场景来给你详细说明，先从最小示例场景入手，再一步步给出解决方案：

先构建最小示例场景 #

假设我们有一张目标表users（存储用户信息），另外两张关联表：orders（有外键约束关联用户）和comments（无外键但逻辑上通过user_id关联用户），表结构如下：

-- 目标表：用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

-- 引用表1：订单表（带外键约束）
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 引用表2：评论表（无外键，仅逻辑关联）
CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    content TEXT
);

场景1：存在外键约束的引用次数统计 #

对于有外键约束的表，我们可以通过子查询统计+LEFT JOIN的方式，获取目标表每行的引用次数，同时保留没有被引用的行（显示0次）：

SELECT 
    u.user_id,
    u.username,
    COALESCE(o.order_count, 0) AS order_count, -- 用COALESCE把NULL转为0
    COALESCE(c.comment_count, 0) AS comment_count,
    COALESCE(o.order_count, 0) + COALESCE(c.comment_count, 0) AS total_references
FROM users u
-- 子查询统计每个用户的订单数量
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id
-- 子查询统计每个用户的评论数量
LEFT JOIN (
    SELECT user_id, COUNT(*) AS comment_count
    FROM comments
    GROUP BY user_id
) c ON u.user_id = c.user_id
ORDER BY u.user_id;

这个查询会返回每个用户的订单数、评论数，以及总引用次数，即使某个用户没有任何订单或评论，对应字段也会显示0。

场景2：无外键但存在逻辑关联的处理 #

如果表之间没有定义外键，但存在逻辑上的关联字段（比如这里的user_id），统计方法和上面类似，但需要注意脏数据问题（比如comments里的user_id在users中不存在）。

方法1：直接统计（包含脏数据） #

如果不需要过滤脏数据，直接用和场景1一样的子查询即可，不过统计出的次数会包含那些无效的关联记录。

方法2：过滤脏数据后的统计 #

如果只想统计有效的关联（即comments的user_id在users中存在），可以在子查询里添加过滤条件：

SELECT 
    u.user_id,
    u.username,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(c.valid_comment_count, 0) AS valid_comment_count,
    COALESCE(o.order_count, 0) + COALESCE(c.valid_comment_count, 0) AS total_references
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS valid_comment_count
    FROM comments
    -- 过滤掉不存在于users表的user_id
    WHERE EXISTS (SELECT 1 FROM users u WHERE u.user_id = comments.user_id)
    GROUP BY user_id
) c ON u.user_id = c.user_id
ORDER BY u.user_id;

额外：找出脏数据 #

如果你想清理这些脏数据，可以单独查询出无效的关联记录：

-- 找出comments中不存在于users的user_id记录
SELECT * FROM comments WHERE user_id NOT IN (SELECT user_id FROM users);

补充：批量统计总引用次数的技巧 #

如果引用表很多，逐个写LEFT JOIN会比较繁琐，你可以用UNION ALL把所有引用表的关联记录合并，再统一统计：

SELECT 
    u.user_id,
    u.username,
    COUNT(r.reference_id) AS total_references
FROM users u
LEFT JOIN (
    -- 合并订单和评论的关联记录
    SELECT user_id, order_id AS reference_id FROM orders
    UNION ALL
    SELECT user_id, comment_id AS reference_id FROM comments
) r ON u.user_id = r.user_id
GROUP BY u.user_id, u.username
ORDER BY u.user_id;

这种方法会直接返回每个用户的总引用次数，代码更简洁，适合引用表数量较多的场景。

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