如何找出SQL Server中ExcludedPositions表ReasonCode为6最多的键组合？

你可以通过分组统计结合排名函数来实现需求，以下是两种常用的实现方式：

方法一：返回所有并列最多的组合 #

如果存在多个HoldingID/CloseOfBusinessDate组合拥有相同的最大ReasonCode=6记录数，该方法会返回所有符合条件的组合：

WITH RankedCombos AS (
    SELECT 
        HoldingID,
        CloseOfBusinessDate,
        COUNT(CASE WHEN ReasonCode = 6 THEN 1 END) AS ReasonCode6Count,
        RANK() OVER (ORDER BY COUNT(CASE WHEN ReasonCode = 6 THEN 1 END) DESC) AS ComboRank
    FROM ExcludedPositions
    GROUP BY HoldingID, CloseOfBusinessDate
)
SELECT 
    HoldingID,
    CloseOfBusinessDate,
    ReasonCode6Count
FROM RankedCombos
WHERE ComboRank = 1;

方法二：仅返回单个组合（若有并列则随机取其一） #

如果只需要返回任意一个拥有最多ReasonCode=6记录数的组合，用ROW_NUMBER()替代RANK()即可：

WITH RankedCombos AS (
    SELECT 
        HoldingID,
        CloseOfBusinessDate,
        COUNT(CASE WHEN ReasonCode = 6 THEN 1 END) AS ReasonCode6Count,
        ROW_NUMBER() OVER (ORDER BY COUNT(CASE WHEN ReasonCode = 6 THEN 1 END) DESC) AS ComboRank
    FROM ExcludedPositions
    GROUP BY HoldingID, CloseOfBusinessDate
)
SELECT 
    HoldingID,
    CloseOfBusinessDate,
    ReasonCode6Count
FROM RankedCombos
WHERE ComboRank = 1;

补充说明 #

• COUNT(CASE WHEN ReasonCode = 6 THEN 1 END) 用于精准统计每个组合下ReasonCode=6的记录数，和SUM(CASE WHEN ReasonCode =6 THEN 1 ELSE 0 END)功能等价
• RANK()会让计数相同的组合共享同一排名，ROW_NUMBER()则会给每个组合分配唯一排名（即使计数相同），可根据实际需求选择

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