SQL Server同表行内单列与多列值比较计数的更优实现方法

对于 SQL Server 2017 版本，直接使用集合运算替代逐行RBAR处理即可，性能可以完全满足300行+15列的业务需求，不需要写循环、游标等逐行处理逻辑。

推荐实现方案 #

核心思路用VALUES子句把同行的多列转成多行后聚合统计，代码易维护，增减比较列时只需要调整列列表即可：

SELECT 
    A,
    (SELECT COUNT(*) 
     -- VALUES 括号内按需求补全所有需要比较的列即可
     FROM (VALUES ([1]), ([2]), ([3])) AS t(col_val) 
     WHERE A > col_val) AS counter
FROM your_table

如果是实际业务的15列，只需要依次把15个列名加入VALUES的括号列表即可，示例：

SELECT 
    A,
    (SELECT COUNT(*) 
     FROM (VALUES ([1]), ([2]), ([3]), ([4]), ([5]),
                   ([6]), ([7]), ([8]), ([9]), ([10]),
                   ([11]), ([12]), ([13]), ([14]), ([15])) AS t(col_val) 
     WHERE A > col_val) AS counter
FROM your_table

备选简化方案 #

如果比较的列数固定，也可以直接用条件判断相加实现，执行效率更高：

SELECT 
    A,
    IIF(A > [1], 1, 0) + 
    IIF(A > [2], 1, 0) + 
    IIF(A > [3], 1, 0) AS counter
FROM your_table

15列场景下依次叠加15个IIF判断即可。

样例执行结果 #

针对你给出的测试数据，两种方案返回结果一致：

结果解释：第一行A=5，仅大于2、3列的0值，计数为2；第二行A=2，大于所有1-3列的值，计数为3，符合需求逻辑。

方案优势 #

• 纯集合运算，无逐行遍历逻辑，针对当前数据量可毫秒级返回结果
• 逻辑简单，无需依赖临时表、表变量等额外对象，单条查询即可完成
• 维护成本低，调整比较列时只需要修改对应列列表即可

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