如何用T-SQL统计NAME在BOSS_PATH中的出现次数并计算COUNT_UNDER

需求说明 #

统计NAME列中每个名称在所有BOSS_PATH列值中的出现次数，通过出现次数-1得到COUNT_UNDER字段。

源数据 #

NAME    BOSS_PATH
---------------------
WIN     WIN
JOHN    WIN|JOHN
DANG    WIN|JOHN|DANG
JOSH    JOSH

期望结果 #

NAME    BOSS_PATH        COUNT_UNDER
--------------------------------------
WIN     WIN                   2
JOHN    WIN|JOHN              1
DANG    WIN|JOHN|DANG         0
JOSH    JOSH                  0

解决方案 #

以下T-SQL查询可实现需求，核心通过自连接+精准字符串匹配统计出现次数：

SELECT
    t.NAME,
    t.BOSS_PATH,
    (COUNT(t2.BOSS_PATH) - 1) AS COUNT_UNDER
FROM
    YourTableName t
CROSS JOIN
    YourTableName t2
WHERE
    -- 精准匹配NAME在BOSS_PATH中的存在（避免部分字符串误匹配）
    CHARINDEX('|' + t.NAME + '|', '|' + t2.BOSS_PATH + '|') > 0
    -- 处理BOSS_PATH仅为单个NAME的特殊场景
    OR (t.NAME = t2.BOSS_PATH AND CHARINDEX('|', t2.BOSS_PATH) = 0)
GROUP BY
    t.NAME, t.BOSS_PATH
ORDER BY
    t.NAME;

逻辑说明

1. 自连接遍历所有组合：用CROSS JOIN将表与自身关联，遍历每一对NAME和BOSS_PATH的组合。
2. 精准字符串匹配：给NAME和BOSS_PATH前后都加分隔符|，避免出现类似NAME='IN'匹配BOSS_PATH='WIN'的错误情况。
3. 统计与计算：分组统计每个NAME的匹配次数，减1后得到COUNT_UNDER（减去的1是NAME自身在BOSS_PATH中的出现）。

注意：将查询中的YourTableName替换为你的实际表名。

内容的提问来源于stack exchange，提问作者Win Wongsawatdichart