如何用T-SQL实现基于PEOPLE与COMPANY分组的服务列选择？能否用row_number？

需求逻辑回顾 #

• 当同一PEOPLE对应多个不同的COMPANY时，选取SERVICE_1作为结果的SERVICE字段
• 当同一PEOPLE对应的COMPANY全部相同时，选取SERVICE_2作为结果的SERVICE字段

是否可以用row_number子句完成？ #

可以，但并非最优选择。需求核心是判断每个PEOPLE下不同COMPANY的数量，用COUNT(DISTINCT COMPANY)窗口函数更直接。若结合row_number实现，需通过额外的分组标记步骤间接完成，会增加不必要的复杂度，因此更推荐基于分组统计的直接实现方式。

T-SQL实现代码 #

1. 创建示例测试表（可选，用于验证） #

CREATE TABLE #TestData (
    PEOPLE VARCHAR(50),
    COMPANY VARCHAR(50),
    SERVICE_1 VARCHAR(50),
    SERVICE_2 VARCHAR(50)
);

INSERT INTO #TestData VALUES
('KRISH', 'AA', 'HYDRO', 'WATER'),
('KRISH', 'BB', NULL, 'WATER'),
('JOHN', 'CC', NULL, 'ROAD'),
('JOHN', 'CC', NULL, 'ELECY'),
('JOHN', 'CC', NULL, 'GAS');

2. 核心查询语句 #

SELECT
    PEOPLE,
    COMPANY,
    CASE
        -- 判断当前PEOPLE是否存在多个不同的COMPANY
        WHEN COUNT(DISTINCT COMPANY) OVER (PARTITION BY PEOPLE) > 1 THEN SERVICE_1
        ELSE SERVICE_2
    END AS SERVICE
FROM #TestData
ORDER BY PEOPLE, COMPANY;

3. 执行结果 #

执行后将得到与期望完全一致的结果：

补充说明 #

• 窗口函数COUNT(DISTINCT COMPANY) OVER (PARTITION BY PEOPLE)用于计算每个PEOPLE对应的不同COMPANY总数，以此作为分支判断的核心依据
• 若坚持用row_number相关逻辑实现，可借助DENSE_RANK标记不同COMPANY的序号，再通过最大序号判断数量，示例代码如下（步骤冗余，仅作参考）：

WITH CTE AS (
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY PEOPLE ORDER BY COMPANY) AS CompanyRank,
        MAX(DENSE_RANK() OVER (PARTITION BY PEOPLE ORDER BY COMPANY)) OVER (PARTITION BY PEOPLE) AS MaxRank
    FROM #TestData
)
SELECT
    PEOPLE,
    COMPANY,
    CASE WHEN MaxRank > 1 THEN SERVICE_1 ELSE SERVICE_2 END AS SERVICE
FROM CTE
ORDER BY PEOPLE, COMPANY;

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