基于WORKS_ON复合主键表，统计各工人的同项目其他工人数

当然可行！这个需求核心是判断两个工人的项目集合完全相等，下面给你两种实用的实现思路，第一种效率更高，适合大多数场景：

方法一：生成项目签名统计（推荐） #

这个方法的核心是给每个工人的项目集合生成一个唯一的排序签名——把参与的项目号按固定顺序拼接成字符串，这样项目完全相同的工人会有一模一样的签名，之后只要统计每个签名的总人数，再减去工人自己就是结果。

MySQL 实现代码 #

WITH worker_project_signatures AS (
    SELECT 
        ID,
        GROUP_CONCAT(ProjectNum ORDER BY ProjectNum SEPARATOR ',') AS project_signature
    FROM WORKS_ON
    GROUP BY ID
),
signature_counts AS (
    SELECT 
        project_signature,
        COUNT(*) AS total_workers
    FROM worker_project_signatures
    GROUP BY project_signature
)
SELECT 
    wps.ID,
    CASE 
        WHEN sc.total_workers > 1 THEN sc.total_workers - 1 
        ELSE 0 
    END AS same_project_count
FROM worker_project_signatures wps
JOIN signature_counts sc ON wps.project_signature = sc.project_signature
ORDER BY wps.ID;

PostgreSQL/SQL Server 实现代码 #

这两个数据库用STRING_AGG替代MySQL的GROUP_CONCAT：

WITH worker_project_signatures AS (
    SELECT 
        ID,
        STRING_AGG(CAST(ProjectNum AS VARCHAR), ',' ORDER BY ProjectNum) AS project_signature
    FROM WORKS_ON
    GROUP BY ID
),
signature_counts AS (
    SELECT 
        project_signature,
        COUNT(*) AS total_workers
    FROM worker_project_signatures
    GROUP BY project_signature
)
SELECT 
    wps.ID,
    GREATEST(sc.total_workers - 1, 0) AS same_project_count
FROM worker_project_signatures wps
JOIN signature_counts sc ON wps.project_signature = sc.project_signature
ORDER BY wps.ID;

代码说明 #

• 第一个CTE worker_project_signatures：给每个工人生成排序后的项目拼接字符串，确保项目顺序不影响签名（比如项目2、3和3、2会生成相同的签名）。
• 第二个CTE signature_counts：统计每个签名对应的工人总数。
• 最后关联两个CTE，每个工人的相同项目人数就是总人数减1（排除自己），如果只有自己一个人，返回0。

方法二：集合直接比较（适合小数据集） #

如果你的数据量不大，也可以用集合的EXCEPT操作直接判断两个工人的项目是否完全一致，不过这种方法效率会低一些：

SELECT 
    w1.ID,
    COUNT(DISTINCT w2.ID) AS same_project_count
FROM WORKS_ON w1
LEFT JOIN WORKS_ON w2 ON w1.ID != w2.ID
GROUP BY w1.ID
HAVING 
    -- w1的所有项目w2都包含
    NOT EXISTS (
        SELECT ProjectNum FROM WORKS_ON WHERE ID = w1.ID
        EXCEPT
        SELECT ProjectNum FROM WORKS_ON WHERE ID = w2.ID
    )
    AND
    -- w2的所有项目w1都包含
    NOT EXISTS (
        SELECT ProjectNum FROM WORKS_ON WHERE ID = w2.ID
        EXCEPT
        SELECT ProjectNum FROM WORKS_ON WHERE ID = w1.ID
    );

用你举的例子测试：ID1和ID3的项目签名都是2,3，总人数是2，所以各自的计数是1；ID2的签名是2，总人数是1，计数是0，完全符合预期。

内容的提问来源于stack exchange，提问作者H. Wilde