SQL实现：筛选同一User对应不同Organisation的记录

问题分析 #

你之前使用ROW_NUMBER() OVER (PARTITION BY user)时，会对同一用户的所有记录按顺序编号，但无法直接区分该用户是否关联多个不同组织——哪怕同一组织的重复记录，编号仍会递增。我们需要先识别出拥有至少2个不同组织的用户，再筛选这些用户的全部关联记录，或者直接标记符合条件的记录。

方法一：关联筛选法（通用SQL方言适配） #

这是最直接的实现方式，适用于所有支持基本SQL语法的数据库：

1. 先提取拥有多组织的用户名单

SELECT user
FROM your_table
GROUP BY user
HAVING COUNT(DISTINCT organisation) > 1

2. 关联原表筛选目标记录

SELECT t.*
FROM your_table t
INNER JOIN (
    SELECT user
    FROM your_table
    GROUP BY user
    HAVING COUNT(DISTINCT organisation) > 1
) AS multi_org_users 
ON t.user = multi_org_users.user

方法二：窗口函数标记法 #

利用窗口聚合函数直接统计每个用户对应的不同组织数量，再筛选符合条件的记录（支持MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH user_org_stats AS (
    SELECT 
        *,
        COUNT(DISTINCT organisation) OVER (PARTITION BY user) AS distinct_org_count
    FROM your_table
)
SELECT * 
FROM user_org_stats 
WHERE distinct_org_count > 1

通用扩展：自定义固定字段分区 #

如果需要按多个固定字段（比如user + department）分区，判断这些字段组合对应的不同组织数量，只需修改PARTITION BY和GROUP BY的字段即可：

关联筛选法示例 #

SELECT t.*
FROM your_table t
INNER JOIN (
    SELECT user, department
    FROM your_table
    GROUP BY user, department
    HAVING COUNT(DISTINCT organisation) > 1
) AS multi_org_groups 
ON t.user = multi_org_groups.user 
AND t.department = multi_org_groups.department

窗口函数法示例 #

WITH group_org_stats AS (
    SELECT 
        *,
        COUNT(DISTINCT organisation) OVER (PARTITION BY user, department) AS distinct_org_count
    FROM your_table
)
SELECT * 
FROM group_org_stats 
WHERE distinct_org_count > 1

为什么之前的ROW_NUMBER()不生效？ #

ROW_NUMBER() OVER (PARTITION BY user)的作用是对同一用户的所有记录（无论组织是否重复）按指定规则排序并递增编号，它无法统计不同组织的数量。要判断用户是否关联多组织，核心是统计去重后的组织数量，因此COUNT(DISTINCT)是更适配的函数。

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