如何获取数据库中Referid值相同的用户列表及详情

我来帮你搞定这个需求——你想要找出所有共享同一个referid的用户的完整信息，但你原来的SQL语句用了GROUP BY，这会把相同referid的用户聚合在一起，没法看到每个用户的具体详情，这显然不是你想要的结果。

问题分析 #

你的核心需求是：找出所有referid值重复（即至少有两个用户使用同一个referid）的用户的全部详情，而不是按referid分组统计数量。

解决方案 #

这里提供两种靠谱的SQL写法，适配不同的数据库场景：

方法1：子查询筛选重复Referid（兼容所有主流数据库）

先通过子查询找出所有出现过至少两次的referid，再关联原表获取这些referid对应的所有用户详情：

SELECT 
    u.userid, 
    u.firstname, 
    u.surname, 
    u.othername, 
    u.username, 
    u.mobilephone, 
    u.email, 
    u.referalid,
    s.stage_description
FROM users u
INNER JOIN stages s ON u.user_stage = s.stage_id
WHERE u.referalid IN (
    SELECT referalid 
    FROM users 
    GROUP BY referalid 
    HAVING COUNT(*) > 1 -- 筛选出至少有2个用户共享的referid
)
ORDER BY u.referalid, u.userid; -- 按referid分组排序，方便查看同组用户

方法2：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等）

用窗口函数直接给每个用户标记其referid对应的总用户数，再筛选出总用户数大于1的记录：

SELECT 
    userid, 
    firstname, 
    surname, 
    othername, 
    username, 
    mobilephone, 
    email, 
    referalid,
    stage_description
FROM (
    SELECT 
        u.userid, 
        u.firstname, 
        u.surname, 
        u.othername, 
        u.username, 
        u.mobilephone, 
        u.email, 
        u.referalid,
        s.stage_description,
        COUNT(*) OVER (PARTITION BY u.referalid) AS referid_count
    FROM users u
    INNER JOIN stages s ON u.user_stage = s.stage_id
) AS user_counts
WHERE referid_count > 1
ORDER BY referalid, userid;

示例结果（基于你提供的测试数据） #

假设stages表中对应的stage_description为"Active"，查询结果会是这样的表格：

为什么你的原SQL不对？ #

你原来的语句GROUP BY users.referalid HAVING COUNT(*) > 0有两个问题：

1. COUNT(*) > 0没有意义，因为每个referid至少对应一个用户，所以会返回所有referid的分组记录；
2. 在严格模式的数据库中，SELECT里的非聚合字段（比如firstname、userid）如果不在GROUP BY里，会直接报错，即使不报错，返回的也只是每个referid的随机一条记录，不是所有用户的详情。

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