SQL Server 2012：按PolicyNumber选取非NULL PolicyType（无需分组）

嘿，这个需求在SQL Server 2012里不用GROUP BY完全可以实现，咱们有两种简单的思路，都能完美解决问题：

方案一：使用窗口函数MAX() OVER() #

窗口函数可以在不分组的情况下，针对每条记录获取同PolicyNumber分组下的非NULL PolicyType值——因为MAX()函数会自动忽略NULL，刚好能提取到我们需要的那个非NULL值。

完整的测试代码如下：

declare @TestTable table ( ID int, PolicyNumber varchar(50), PolicyType varchar(50) )
insert into @TestTable values 
(001, 'ENV1','Primary'), 
(002, 'ENV1',NULL), 
(003, 'ENV2','Claim Made'), 
(004, 'ENV3','Claim Made'), 
(005, 'ENV3',NULL);

-- 核心查询
SELECT 
    ID,
    PolicyNumber,
    -- 当PolicyType为NULL时，取同PolicyNumber下的非NULL值，否则保留原值
    ISNULL(PolicyType, MAX(PolicyType) OVER (PARTITION BY PolicyNumber)) AS PolicyType
FROM @TestTable;

逻辑说明： #

• MAX(PolicyType) OVER (PARTITION BY PolicyNumber)：对每个PolicyNumber做窗口分组（不是GROUP BY的聚合分组），计算该组内PolicyType的最大值——因为每个PolicyNumber下的非NULL值唯一，所以MAX就是我们要的目标值。
• ISNULL()函数用来判断当前记录的PolicyType是否为NULL，是则替换为窗口函数得到的值，否则保留原内容。

方案二：使用关联子查询 #

如果对窗口函数不太熟悉，也可以用关联子查询的方式，针对每条记录去查询同PolicyNumber下的非NULL PolicyType值，同样不需要分组：

declare @TestTable table ( ID int, PolicyNumber varchar(50), PolicyType varchar(50) )
insert into @TestTable values 
(001, 'ENV1','Primary'), 
(002, 'ENV1',NULL), 
(003, 'ENV2','Claim Made'), 
(004, 'ENV3','Claim Made'), 
(005, 'ENV3',NULL);

-- 核心查询
SELECT 
    t.ID,
    t.PolicyNumber,
    ISNULL(t.PolicyType, 
        (SELECT TOP 1 PolicyType FROM @TestTable 
         WHERE PolicyNumber = t.PolicyNumber AND PolicyType IS NOT NULL)
    ) AS PolicyType
FROM @TestTable t;

逻辑说明： #

• 子查询(SELECT TOP 1 PolicyType FROM @TestTable WHERE PolicyNumber = t.PolicyNumber AND PolicyType IS NOT NULL)会找到当前记录同PolicyNumber下的第一条非NULL PolicyType值。
• 同样用ISNULL()完成NULL值的替换，保证结果符合需求。

这两种方案都不需要使用GROUP BY，而且在SQL Server 2012里都能正常运行，你可以根据自己的习惯选择其中一种~

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