SQL技术问询：如何按值筛选Top5与Bottom5列并获取列名？

需求说明 #

• 按每行中各列的数值，从指定列列表里选出Top5（数值最大的5个）和Bottom5（数值最小的5个）对应的列名
• 若多列数值相同，可任选其一

示例数据 #

CREATE TABLE #b(Company VARCHAR(10),A1 INT,A2 INT,A3 INT,A4 INT,B1 INT,G1 INT,G2 INT,G3 INT,HH5 INT,SS6 INT)

INSERT INTO #b
SELECT 'test_A',8,10,6,10,0,6,0,6,13,4 UNION ALL
SELECT 'test_B',17,7,0,1,3,18,0,6,9,5 UNION ALL
SELECT 'test_C',0,0,6,1,2,6,3,4,3,2 UNION ALL
SELECT 'test_D',13,1,4,1,4,1,9,0,0,5

SELECT * FROM #b

期望输出 #

当前困境 #

仅能查询出列的最大值，无法关联获取对应的列名，现有代码如下：

SELECT Company,(  SELECT MAX(myval)   FROM (VALUES (A1),(A2),(A3),(A4),(B1),(G1),(G2),(G3),(HH5)) AS temp(myval)) AS MaxOfColumns
FROM #b

解决方案 #

要实现提取列名并排序，需先将列转行，再按公司分组排序，最后拼接目标列名。完整SQL代码如下：

WITH ColumnValues AS (
    SELECT 
        Company,
        ColumnName,
        ColumnValue
    FROM #b
    UNPIVOT (
        ColumnValue FOR ColumnName IN (A1,A2,A3,A4,B1,G1,G2,G3,HH5,SS6)
    ) AS UnpivotData
),
RankedValues AS (
    SELECT 
        Company,
        ColumnName,
        ColumnValue,
        -- 按数值降序生成排名，标记Top5
        ROW_NUMBER() OVER (PARTITION BY Company ORDER BY ColumnValue DESC) AS TopRank,
        -- 按数值升序生成排名，标记Bottom5
        ROW_NUMBER() OVER (PARTITION BY Company ORDER BY ColumnValue ASC) AS BottomRank
    FROM ColumnValues
)
SELECT 
    Company,
    -- 按排名拼接Top5列名
    STRING_AGG(CASE WHEN TopRank <=5 THEN ColumnName END, ',') WITHIN GROUP (ORDER BY TopRank) AS Top5,
    -- 按排名拼接Bottom5列名
    STRING_AGG(CASE WHEN BottomRank <=5 THEN ColumnName END, ',') WITHIN GROUP (ORDER BY BottomRank) AS Bottom5
FROM RankedValues
GROUP BY Company

代码说明 #

1. UNPIVOT：将每行的列数据转换为行数据，得到每个公司对应的列名与列值的明细
2. ROW_NUMBER()：按公司分组，分别按列值降序、升序生成排名，筛选出Top5和Bottom5的记录
3. STRING_AGG()：将符合条件的列名按排名顺序拼接成字符串，生成最终结果

注意：STRING_AGG函数适用于SQL Server 2016及以上版本；若使用更低版本，可改用STUFF结合FOR XML PATH的方式实现字符串拼接。

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