如何在SQL Server 2019中按多条件清理临时表无效数据

SQL Server版本：Sql Server 2019 - 15.04138.2

CREATE TABLE #data 
(
    Device varchar(100), 
    Hall INT, 
    EquipNo INT, 
    LocNo INT, 
    HitCount INT, 
    Operator VARCHAR(100)
)

INSERT INTO #data
    VALUES 
          ('Tiger', 0, 0, 0, 0, null)
        , ('Tiger', 1, 0, 10, 0, NULL)
        , ('Tiger', 1, 5, 10, 0, NULL)
        , ('Tiger', 1, 5, 10, 0, NULL)
        , ('Tiger', 1, 5, 10, 3, NULL)
        , ('Tiger', 1, 5, 10, 3, 'Sam')  

        , ('Shark', 0, 0, 0, 0, null)
        , ('Shark', 2, 3, 0, 0, null) 
        , ('Shark', 2, 3, null, 5, null) 
        , ('Shark', 2, 3, 20, 2, null)
        , ('Shark', 2, 3, 20, 2, 'Alex')

        , ('Tiger', 0, 0, 0, 0, null)
        , ('Tiger', 1, 3, 0, 0, null) 
        , ('Tiger', 1, null, null, 5, null)
        , ('Tiger', 1, 3, 20, 10, 'Sam') 
        , ('Tiger', 1, 3, 20, 2, 'Sam')

• 有效记录需满足：Device不为空字符串，Hall、EquipNo不为0，HitCount不为0
• 按Device、Hall、EquipNo分组，组内优先选择HitCount最高的记录
• 若HitCount相同，则选择非空字段数量最多的记录

使用ROW_NUMBER()的解决方案时，结果包含无效记录（如Hall或EquipNo为0的行），需修正方案以得到符合要求的结果，允许使用多个临时表。

先过滤掉所有无效记录，再对有效记录进行分组排序，具体代码如下：

1. 筛选有效记录：创建临时表存储符合规则的行，直接排除无效数据

SELECT *
INTO #valid_data
FROM #data
WHERE 
    Device IS NOT NULL 
    AND Device <> ''
    AND Hall <> 0
    AND EquipNo <> 0
    AND HitCount <> 0;

2. 分组排序并提取目标记录：计算每条有效记录的非空字段数，用ROW_NUMBER()按规则排序后，取每个分组的第一条

WITH ranked_data AS (
    SELECT 
        *,
        -- 统计LocNo和Operator的非空数量，满足"非空字段最多"的筛选要求
        (IIF(LocNo IS NOT NULL, 1, 0) + IIF(Operator IS NOT NULL, 1, 0)) AS NonNullFieldCount,
        ROW_NUMBER() OVER (
            PARTITION BY Device, Hall, EquipNo
            ORDER BY HitCount DESC, NonNullFieldCount DESC
        ) AS RowNum
    FROM #valid_data
)
SELECT 
    Device, Hall, EquipNo, LocNo, HitCount, Operator
FROM ranked_data
WHERE RowNum = 1;

执行以上代码后，即可得到与期望结果完全一致的输出。

内容的提问来源于stack exchange，提问作者Saleh Al Abbas