MySQL如何查询未创建KB的用户并关联KB描述信息

问题分析 #

你的现有查询存在两个核心问题：

1. NOT EXISTS子句逻辑错误：子查询中通过WHERE u2.id = lkb2.created_by_id筛选出所有创建过KB的用户，只要存在这类用户（比如agent01），NOT EXISTS就会返回false，导致整个查询无结果。
2. 字段引用错误：lkb.name应该是lkb.k_b_id，因为log_k_b表的对应字段是k_b_id而非name。

另外你描述的需求存在矛盾：未在log_k_b中创建过KB的用户本身没有对应的KB记录，所以这类用户的KB相关字段必然为NULL。如果你的真实需求是以下两种场景之一，可参考对应方案：

场景1：仅查询未创建过KB的用户 #

这类用户的KB字段会显示NULL，这是合理的，因为他们没有创建过任何KB：

SELECT u.id AS userId, u.user_name AS userName, 
       lkb.k_b_id AS kbId, lkb.description AS kbName, lkb.created_by_id
FROM user u
LEFT JOIN log_k_b lkb ON u.id = lkb.created_by_id
WHERE NOT EXISTS (
    SELECT 1 FROM log_k_b lkb2 WHERE lkb2.created_by_id = u.id
)
AND u.deleted = 0; -- 过滤已删除用户，可根据实际业务调整

或者用NOT IN实现（注意处理created_by_id为NULL的情况）：

SELECT u.id AS userId, u.user_name AS userName, 
       lkb.k_b_id AS kbId, lkb.description AS kbName, lkb.created_by_id
FROM user u
LEFT JOIN log_k_b lkb ON u.id = lkb.created_by_id
WHERE u.id NOT IN (
    SELECT DISTINCT created_by_id FROM log_k_b WHERE created_by_id IS NOT NULL
)
AND u.deleted = 0;

场景2：查询所有用户，同时展示他们创建的KB信息 #

如果需要列出所有用户，创建过KB的用户显示对应KB数据，未创建的显示NULL，直接使用左连接即可：

SELECT u.id AS userId, u.user_name AS userName, 
       lkb.k_b_id AS kbId, lkb.description AS kbName, lkb.created_by_id
FROM user u
LEFT JOIN log_k_b lkb ON u.id = lkb.created_by_id
WHERE u.deleted = 0;

如果每个用户只需要显示最新的一条KB记录（假设id越大表示创建时间越晚），可以用窗口函数实现：

SELECT u.id AS userId, u.user_name AS userName, 
       lkb.k_b_id AS kbId, lkb.description AS kbName, lkb.created_by_id
FROM user u
LEFT JOIN (
    SELECT created_by_id, k_b_id, description, created_by_id,
           ROW_NUMBER() OVER (PARTITION BY created_by_id ORDER BY id DESC) AS rn
    FROM log_k_b
) lkb ON u.id = lkb.created_by_id AND lkb.rn = 1
WHERE u.deleted = 0;

内容的提问来源于stack exchange，提问作者Burahan T.