tsql - 根据值从列列表中选择前 5 列和后 5 列

Question

我有一个要求，我需要根据列的值从列列表中选择前 5 列和后 5 列。如果超过 1 列具有相同的值，则从中选择任意一列。

例如

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

期望的输出:

<表类="s-表"><头>公司Top5Bottom5<正文>test_AHH5,A2,A1,A3,SS6B1,SS6,A3,A1,A2test_BG1,A1,HH5,A2,G3A3,A4,B1,SS6,G3

我能够找到最高值，但不能找到列名。这是我被困的地方，我能够找到最大分数，但不确定如何找到包含该最大值的列。

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

Answer 1

正如 Larnu 所建议的，第一步是将数据UNPIVOT 转换为类似 (Company, ColumnName, Value) 的形式。然后，您可以使用 ROW_NUMBER() 窗口函数根据排序后的值将序号 1 - 10 分配给每个公司的每个值。

接下来，您可以将以上内容包装在公用表表达式 (CTE) 中以提供查询，对于每个公司，该查询使用条件聚合和 STRING_AGG() 有选择地组合前 5 名和底部 5 列名称以产生所需的结果。

类似于:

;WITH Data AS (
    SELECT
        Company,
        ColumnName,
        Value,
        ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Value DESC, ColumnName) AS Ord
    FROM #b
    UNPIVOT ( 
        Value FOR ColumnName IN (A1, A2, A3, A4, B1, G1, G2, G3, HH5, SS6)
    ) U
)
SELECT
    D.Company,
    STRING_AGG(CASE WHEN D.Ord BETWEEN 1 AND 5 THEN D.ColumnName END, ', ')
        WITHIN GROUP (ORDER BY D.ORD) AS Top5,
    STRING_AGG(CASE WHEN D.Ord BETWEEN 6 AND 10 THEN D.ColumnName END, ', ')
        WITHIN GROUP (ORDER BY D.ORD) AS Bottom5
FROM Data D
GROUP BY D.Company
ORDER BY D.Company

对于不支持 STRING_AGG() 的旧 SQL Server 版本，FOR XML PATH(''),TYPE 结构可用于连接文本。然后使用 .value('text()[1]', 'varchar(max)') 函数从 XML 中安全地提取结果，最后使用 STUFF() 函数用于去除前导分隔符(逗号空格)。

;WITH Data AS (
    SELECT
        Company,
        ColumnName,
        Value,
        ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Value DESC, ColumnName) AS Ord
    FROM #b
    UNPIVOT ( 
        Value FOR ColumnName IN (A1, A2, A3, A4, B1, G1, G2, G3, HH5, SS6)
    ) U
)
SELECT B.Company, C.Top5, C.Bottom5
FROM #b B
CROSS APPLY (
    SELECT
        STUFF((
            SELECT ', ' + D.ColumnName
            FROM Data D
            WHERE D.Company = B.Company
            AND D.Ord BETWEEN 1 AND 5
            ORDER BY D.ORD
            FOR XML PATH(''),TYPE
        ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS Top5,
        STUFF((
            SELECT ', ' + D.ColumnName
            FROM Data D
            WHERE D.Company = B.Company
            AND D.Ord BETWEEN 6 AND 10
            ORDER BY D.ORD
            FOR XML PATH(''),TYPE
        ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS Bottom5
) C
ORDER BY B.Company

参见 this db<>fiddle一个演示。

如果您还想要前 5 个值和后 5 个值的列表，您可以重复上面的聚合，同时将 CONVERT(VARCHAR, D.Value) 替换为 D.ColumnName 在适当的地方。