sql-server-2008 - SQL Server : Greatest-N-per-group expanded

Question

我的 table 看起来像:

A     B     C     D
1     1     1     1
1     1     3     2
1     1     0     4
1     1     2     1     
1     2     1     0
1     2     0     2
1     2     4     5   
2     1     5     3

我的目标是，对于每一对 A 和 B，输出 D 中对应于 MIN(C) 的值，以及 D 中对应于 MAX(C) 的值。输出应该是

A    B    D at MIN(C)    D at MAX(C)
1    1    4              2
1    2    2              5
2    1    3              3

我知道要提取 MIN(C) 和 MAX(C) 我只是这样做:

SELECT A, B, MIN(C) as "minC", MAX(C) as "maxC"
FROM Table
GROUP BY A, B
ORDER BY A, B

我的问题是:我如何带着 D 列一起骑行？如果我将它包含在 SELECT 和 GROUP BY 子句中，它将为每个 D 生成 MIN(C) 和 MAX(C)，这不是我想要的。此外，我什至不需要输出 MIN(C) 和 MAX(C)。 D 就是我所追求的。

SQL Select only rows with Max Value on a Column 中提供的基本大纲似乎无法处理这种情况。

提前致谢!

Answer 1

您的查询可能如下所示:

;with C as
(
  select A, B, C, D,
         row_number() over(partition by A, B order by C asc) as rn1,
         row_number() over(partition by A, B order by C desc) as rn2
  from YourTable
)
select C1.A, C1.B, C1.D as "D at MIN(C)", C2.D as "D at MAX(C)"
from C as C1
  inner join C as C2
    on C1.A = C2.A and
       C1.B = C2.B
where C1.rn1 = 1 and
      C2.rn2 = 1

第一部分是 common table expression (CTE)它封装了一个查询，稍后可以在主查询中重用。它使用 row_number()枚举每个分区中的行。 rn1由 C asc 订购所以 rn1 = 1对于 C 的最小值和 rn2由 C desc 订购这意味着 rn2 = 1对于 C 的最大值.主要查询是在 A 上使用 CTE 两次连接。和 B列。 where 子句确保我们只得到行 where rn1和 rn2是 1 .

这是一个使用表变量 @T 的工作示例而不是你的 table 。

declare @T table
(
  A int,
  B int,
  C int,
  D int
)

insert into @T values
(1,     1,     1,     1),
(1,     1,     3,     2),
(1,     1,     0,     4),
(1,     1,     2,     1),   
(1,     2,     1,     0),
(1,     2,     0,     2),
(1,     2,     4,     5), 
(2,     1,     5,     3)

;with C as
(
  select A, B, C, D,
         row_number() over(partition by A, B order by C asc) as rn1,
         row_number() over(partition by A, B order by C desc) as rn2
  from @T
)
select C1.A, C1.B, C1.D as "D at MIN(C)", C2.D as "D at MAX(C)"
from C as C1
  inner join C as C2
    on C1.A = C2.A and
       C1.B = C2.B
where C1.rn1 = 1 and
      C2.rn2 = 1