如何找到与列的最小值/最大值关联的行?

Question

所以基本上我有一些简单的SQL代码，如下所示;

SELECT 
[Column1]
,[Column2]
,[Column3]
,[Column4]
,MIN([Column5]) AS maxColumn5
,MAX([Column6]) AS minColumn6
,SUM([Column7]) AS sumColumn7
,SUM([Column8]) AS sumColumn8
,SUM([Column9]) AS sumColumn9
FROM 
[tableName]
GROUP BY 
[Column1]
,[Column2]
,[Column3]
,[Column4]

我正在尝试做的是找到对应于 MIN([Column6]( 的"列 1"、"列 2"或"列 3"，然后找到对应于 MAX([列 8](的列。

输出应该完全相同，除了末尾会有一个额外的 2 列指定 min 和 max 与哪一个相关联。

Answer 1

我认为您的问题中有一个简单的问题，因为直接显示对应于最大值或最小值的Col1，Col2，Col3，换句话说，当您按Col1，Col2，Col3和Col4分组时，您拥有它们。 由于您没有提供一些数据，我将设置一些随机数据来证明我的观点。 让我们创建一个类似于您的 9 列的内存表，并用 10 行的 col6-8 的随机数据填充它，例如，您可以使用以下内容：-

Declare @data Table(
Column1 int,Column2 int,Column3 int,Column4 int,Column5 int,Column6 int,Column7 int,Column8 int,Column9 int
)
declare @index int=5
while(@index>0)
begin
insert into @data values(1,2,3,4,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000)
insert into @data values(5,6,7,8,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000)
set @index=@index-1
end

我们可以看到以下数据

select * from @data  order BY [Column1],[Column2],[Column3],[Column4]
Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9
1   2   3   4   669 203 278 364 577
1   2   3   4   389 316 290 548 661
1   2   3   4   835 555 942 985 604
1   2   3   4   477 743 580 305 414
1   2   3   4   431 296 471 150 352
1   2   3   4   346 220 573 941 633
1   2   3   4   392 450 652 978 883
1   2   3   4   235 479 751 136 978
1   2   3   4   906 183 141 915 783
1   2   3   4   329 342 682 977 870
5   6   7   8   218 740 41  299 816
5   6   7   8   800 630 674 888 799
5   6   7   8   27  307 446 743 345
5   6   7   8   501 928 824 592 691
5   6   7   8   439 624 260 757 547
5   6   7   8   287 610 287 708 652
5   6   7   8   441 711 433 642 343
5   6   7   8   751 928 237 53  535
5   6   7   8   594 768 708 173 33
5   6   7   8   352 703 943 867 661

现在让我们看看您提供的分组结果，没有任何更改

Col1    Col2    Col3    Col4    minCol5 maxCol6 maxCol8 sumCol7 sumCol8 sumCol9
1      2       3       4        235   743      985     5360    6299    6755
5      6       7       8        27    928      888     4853    5722    5422

因此，如果我们回到您的问题，对于maxCol6，Col1，Col2，Col3的值是多少，对于每个maxCol6，您都有Col1，Col2，Col3甚至Col4的值。 那么 maxCol16 的 Col1，Col2，Col3 的值是多少，即 928，它们是 5,6 和 7。

好的，现在假设您想要具有maxCol6的记录键，这也很容易，我们将添加一个身份列作为ID，如下所示：-

Declare @data Table(
ID int identity(1,1), Column1 int,Column2 int,Column3 int,Column4 int,Column5 int,Column6 int,Column7 int,Column8 int,Column9 int
)
declare @index int=10
while(@index>0)
begin
insert into @data values(1,2,3,4,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000)
insert into @data values(5,6,7,8,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000,RAND()*1000)
set @index=@index-1
end
select * from @data  order BY [Column1],[Column2],[Column3],[Column4]
;with agg as (
SELECT 
[Column1]
,[Column2]
,[Column3]
,[Column4]
,MIN([Column5]) AS minColumn5
,MAX([Column6]) AS maxColumn6
,MAX([Column8]) AS maxColumn8
,SUM([Column7]) AS sumColumn7
,SUM([Column8]) AS sumColumn8
,SUM([Column9]) AS sumColumn9
FROM 
@data [tableName]
GROUP BY 
[Column1]
,[Column2]
,[Column3]
,[Column4]
)
--select * from agg order BY [Column1],[Column2],[Column3],[Column4]
select agg.*,maxCol6.ID [MaxCol6Seq],maxCol8.ID [MaxCol8Seq]  from agg 
inner join @data maxCol6
on      agg.Column1=maxCol6.Column1 
and agg.Column2=maxCol6.Column2
and agg.Column3=maxCol6.Column3 
and agg.Column4=maxCol6.Column4 
and agg.maxColumn6=maxCol6.Column6
inner join @data maxCol8
on      agg.Column1=maxCol8.Column1 
and agg.Column2=maxCol8.Column2
and agg.Column3=maxCol8.Column3 
and agg.Column4=maxCol8.Column4 
and agg.maxColumn8=maxCol8.Column8

由于这是这组数据的新运行，因此：-

ID  Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9
1   1   2   3   4   201 848 993 50  304
3   1   2   3   4   497 207 644 399 104
5   1   2   3   4   445 321 822 151 185
7   1   2   3   4   611 402 620 61  543
9   1   2   3   4   460 409 182 915 211
11  1   2   3   4   886 804 180 213 282
13  1   2   3   4   614 709 932 806 162
15  1   2   3   4   795 752 110 474 463
17  1   2   3   4   737 545 77  648 727
19  1   2   3   4   788 862 266 464 851
20  5   6   7   8   218 561 943 572 54
18  5   6   7   8   741 621 610 214 536
16  5   6   7   8   579 248 374 693 761
14  5   6   7   8   866 415 198 528 657
12  5   6   7   8   905 947 500 50  387
10  5   6   7   8   492 860 948 299 220
8   5   6   7   8   861 328 727 40  327
6   5   6   7   8   435 534 707 769 777
4   5   6   7   8   587 68  45  184 614
2   5   6   7   8   189 24  289 121 772

结果如下：-

C1  C2  C3  C4  minC5   maxC6   maxC8   sumC7   sumC8   sumC9   MaxCol6Seq  MaxCol8Seq
1   2   3   4   201      862    915      4826   4181    3832    19           9
5   6   7   8   189      947    769      5341   3470    5105    12           6

希望这有帮助。

Answer 2

如果您只想在每行上放置一个标志，指定该值是总体最大值还是最小值，则可以使用窗口函数并CASE：

SELECT [Column1], [Column2], [Column3], [Column4],
MAX([Column5]) AS maxColumn5,
MIN([Column6]) AS minColumn6,
SUM([Column7]) AS sumColumn7,
SUM([Column8]) AS sumColumn8,
SUM([Column9]) AS sumColumn9,
(CASE WHEN MIN([Column6]) = MIN(MIN([Column6])) OVER () THEN 1 ELSE 0 END) as is_min_column6,
(CASE WHEN MAX([Column7]) = MAX(MAX([Column7])) OVER () THEN 1 ELSE 0 END) as is_max_column7
FROM [tableName]
GROUP BY [Column1], [Column2], [Column3], [Column4]