sql - 将多行数据聚合成单行

Question

在我的表中，每一行都有一些数据列 Priority 列(例如，时间戳或只是一个整数)。我想按 ID 对我的数据进行分组，然后在每个组中采用最新的非空列。例如我有下表:

id  A       B       C       Priority
1   NULL    3       4       1
1   5       6       NULL    2
1   8       NULL    NULL    3
2   634     346     359     1
2   34      NULL    734     2

期望的结果是:

id  A   B   C   
1   8   6   4   
2   34  346 734 

在这个示例中，表格很小，只有 5 列，但在实际表格中，它会大得多。我真的希望这个脚本能快速运行。我尝试自己做，但我的脚本适用于 SQLSERVER2012+，所以我删除了它，因为它不适用。

数字:表可以有 150k 行，20 列，20-80k 的唯一 id 和平均 SELECT COUNT(id) FROM T GROUP BY ID 是 2..5

现在我有一个工作代码(感谢@ypercubeᵀᴹ)，但它在大表上运行非常缓慢，在我的情况下，脚本可能需要一分钟甚至更长时间(使用索引等)。

如何加速？

SELECT 
    d.id,
    d1.A,
    d2.B,
    d3.C
FROM 
    ( SELECT id
      FROM T
      GROUP BY id
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) A
      FROM T 
      WHERE id = d.id
        AND A IS NOT NULL
      ORDER BY priority DESC
    ) AS d1 
  OUTER APPLY
    ( SELECT TOP (1) B
      FROM T 
      WHERE id = d.id
        AND B IS NOT NULL
      ORDER BY priority DESC
    ) AS d2 
  OUTER APPLY
    ( SELECT TOP (1) C
      FROM T 
      WHERE id = d.id
        AND C IS NOT NULL
      ORDER BY priority DESC
    ) AS d3 ;

在我拥有真实数据量的测试数据库中，我得到以下执行计划:

Answer 1

这应该可以解决问题，所有的 0 次方都将返回 1，null 除外:

DECLARE @t table(id int,A int,B  int,C int,Priority int)
INSERT @t
VALUES (1,NULL,3   ,4   ,1),
(1,5   ,6   ,NULL,2),(1,8   ,NULL,NULL,3),
(2,634 ,346 ,359 ,1),(2,34  ,NULL,734 ,2)

;WITH CTE as
(
  SELECT id, 
  CASE WHEN row_number() over 
    (partition by id order by Priority*power(A,0) desc) = 1 THEN A END A,
  CASE WHEN row_number() over 
    (partition by id order by Priority*power(B,0) desc) = 1 THEN B END B,
  CASE WHEN row_number() over 
    (partition by id order by Priority*power(C,0) desc) = 1 THEN C END C
  FROM @t
)
SELECT id, max(a) a, max(b) b, max(c) c
FROM CTE
GROUP BY id

结果:

id  a   b   c
1   8   6   4
2   34  346 734