关于为所有活跃用户添加内容状态虚拟字段的SQL咨询

你的需求完全能实现，而且得调整下原查询的逻辑——原SQL用内连接的方式会漏掉那些活跃但从未创建/更新过任何帖子或文章的用户，所以我们要改写写法来覆盖所有活跃用户，同时通过条件判断生成status字段。

推荐方案：用EXISTS+CASE实现（高效且简洁） #

这种方式利用半连接查询，性能通常比连接后去重更好，因为只要找到匹配的记录就会停止检索：

SELECT 
    u.id,
    u.firstname,
    u.lastname,
    -- 条件判断生成status字段
    CASE
        -- 判断用户是否有过文章的创建/更新操作
        WHEN EXISTS (
            SELECT 1 
            FROM article a
            WHERE a.created_by_id = u.id OR a.updated_by_id = u.id
        ) 
        -- 或者是否有过帖子的创建/更新操作
        OR EXISTS (
            SELECT 1 
            FROM post p
            WHERE p.created_by_id = u.id OR p.updated_by_id = u.id
        ) 
        THEN 'has content'
        ELSE 'active'
    END AS status
FROM user u
WHERE u.is_active = true
-- 按用户唯一标识分组，兼容不同数据库的分组规则
GROUP BY u.id, u.firstname, u.lastname;

另一种方案：左连接+去重（更直观） #

如果你更习惯用连接的方式，可以先把所有有操作记录的用户ID提取出来，再左连接到用户表：

SELECT 
    u.id,
    u.firstname,
    u.lastname,
    CASE
        -- 只要文章或帖子操作记录存在，就标记为has content
        WHEN a.user_id IS NOT NULL OR p.user_id IS NOT NULL THEN 'has content'
        ELSE 'active'
    END AS status
FROM user u
-- 左连接所有有文章操作的用户
LEFT JOIN (
    SELECT DISTINCT created_by_id AS user_id FROM article
    UNION
    SELECT DISTINCT updated_by_id AS user_id FROM article
) a ON a.user_id = u.id
-- 左连接所有有帖子操作的用户
LEFT JOIN (
    SELECT DISTINCT created_by_id AS user_id FROM post
    UNION
    SELECT DISTINCT updated_by_id AS user_id FROM post
) p ON p.user_id = u.id
WHERE u.is_active = true
GROUP BY u.id, u.firstname, u.lastname;

关键说明 #

1. 原查询的问题：内连接会自动过滤掉没有匹配文章/帖子记录的用户，所以拿不到仅活跃无内容操作的用户；
2. CASE函数是SQL标准语法，几乎所有关系型数据库都支持；
3. EXISTS的性能优势：对于大表来说，它不需要返回完整的匹配数据，只要确认存在匹配项就会终止子查询，比连接后去重更高效。

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