如何用SQL计算不同部门组合间的共同用户数量

针对你提出的需求——从包含dept和user字段的数据表出发，先获取去重的部门-用户组合，再计算所有部门对（含部门自身）的共同用户数量，我整理了一套可行的SQL实现方案，下面一步步拆解说明：

1. 先明确数据基础 #

首先咱们把原始数据和去重后的结果梳理清楚，方便后续理解：

原始表数据（假设表名为user_dept） #

dept | user
1    | 33
1    | 33
1    | 45
2    | 11
2    | 12
3    | 33
3    | 15

去重部门-用户组合的SQL #

你提到的去重语句完全没问题，执行后得到干净的部门-用户关联数据：

SELECT DISTINCT dept, user FROM user_dept;

去重结果 #

dept | user
1    | 33
1    | 45
2    | 11
2    | 12
3    | 33
3    | 15

2. 核心：统计所有部门对的共同用户数 #

要得到所有部门组合（含自身）的共同用户数，我们可以用CTE临时表+自连接+条件聚合的方式实现，具体分两种场景：

场景1：输出部门对和对应数量的列表形式 #

如果需要先查看每个部门对的统计结果，用下面的SQL：

WITH unique_dept_user AS (
    -- 存储去重后的部门-用户数据
    SELECT DISTINCT dept, user FROM user_dept
),
all_dept_pairs AS (
    -- 生成所有可能的部门对（包括自身）
    SELECT a.dept AS dept_a, b.dept AS dept_b
    FROM (SELECT DISTINCT dept FROM unique_dept_user) a
    CROSS JOIN (SELECT DISTINCT dept FROM unique_dept_user) b
)
SELECT 
    CONCAT('dep_', dept_a, '_', dept_b) AS dept_pair,
    COUNT(u2.user) AS common_users
FROM all_dept_pairs dp
LEFT JOIN unique_dept_user u1 ON dp.dept_a = u1.dept
LEFT JOIN unique_dept_user u2 ON dp.dept_b = u2.dept AND u1.user = u2.user
GROUP BY dp.dept_a, dp.dept_b
ORDER BY dp.dept_a, dp.dept_b;

代码解释：

• unique_dept_user：先把去重后的部门-用户数据存为临时表，避免重复计算
• all_dept_pairs：通过笛卡尔积生成所有部门的两两组合（比如(1,1)、(1,2)、(3,2)等）
• 最后通过两次左连接，匹配两个部门共同的用户，统计数量就是该部门对的共同用户数

场景2：输出你要求的横向列格式 #

如果需要直接得到你给出的那种一行多列的输出（每个部门对作为列，对应数值为行），可以用**条件聚合（PIVOT）**的方式，适配大多数支持标准SQL的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH unique_dept_user AS (
    SELECT DISTINCT dept, user FROM user_dept
),
all_dept_pairs AS (
    SELECT a.dept AS dept_a, b.dept AS dept_b, COUNT(u2.user) AS common_users
    FROM (SELECT DISTINCT dept FROM unique_dept_user) a
    CROSS JOIN (SELECT DISTINCT dept FROM unique_dept_user) b
    LEFT JOIN unique_dept_user u1 ON a.dept = u1.dept
    LEFT JOIN unique_dept_user u2 ON b.dept = u2.dept AND u1.user = u2.user
    GROUP BY a.dept, b.dept
)
SELECT 
    MAX(CASE WHEN dept_a=1 AND dept_b=1 THEN common_users END) AS dep_1_1,
    MAX(CASE WHEN dept_a=1 AND dept_b=2 THEN common_users END) AS dep_1_2,
    MAX(CASE WHEN dept_a=1 AND dept_b=3 THEN common_users END) AS dep_1_3,
    MAX(CASE WHEN dept_a=2 AND dept_b=2 THEN common_users END) AS dep_2_2,
    MAX(CASE WHEN dept_a=2 AND dept_b=1 THEN common_users END) AS dep_2_1,
    MAX(CASE WHEN dept_a=2 AND dept_b=3 THEN common_users END) AS dep_2_3,
    MAX(CASE WHEN dept_a=3 AND dept_b=1 THEN common_users END) AS dep_3_1,
    MAX(CASE WHEN dept_a=3 AND dept_b=2 THEN common_users END) AS dep_3_2,
    MAX(CASE WHEN dept_a=3 AND dept_b=3 THEN common_users END) AS dep_3_3
FROM all_dept_pairs;

执行后就能得到你想要的结果：

dep_1_1 | dep_1_2 | dep_1_3 | dep_2_2 | dep_2_1 | dep_2_3 | dep_3_1 | dep_3_2 | dep_3_3
--------|---------|---------|---------|---------|---------|---------|---------|---------
2       | 0       | 1       | 2       | 0       | 0       | 1       | 0       | 2

补充说明：

如果你的部门数量是动态变化的，上面的静态条件聚合就不太方便，这时候可以用动态SQL来自动生成列名和条件，但具体语法会因数据库类型略有不同（比如MySQL的预处理语句、PostgreSQL的EXECUTE等）。如果部门数量固定，上面的静态写法完全够用。

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