SQL从聚合函数获取其他行 [英] SQL Get Other Rows From Aggregate Function

问题描述

我有一个聚合函数，可以按(col A)进行分组.它从一组列(列B)中选择最大值，但我也想从同一行中的列(列C)中返回另一个值.但是，如果将3行分组，则会从C列中选择第一个值，而不是最大值(MAX(col B))的列.

I have an aggregate function that does a group by (col A). It selects the maximum value from a set of columns(col B), but I also want to return another value from a column in the same row(col C). But if it groups 3 rows it selects the first value from column C not the column with the maximum (MAX(col B)).

A    B    C
1     75  jkl
1    100  abc
1    125  dae
2    200  def
3    300  ghi

"SELECT A, MAX(B), C FROM myTable where B > 50 GROUP BY A"

returns (first row) A => 1, B => 125, C => jkl

I want it to return 

A => 1, B => 125, C => dae

推荐答案

您将要使用一个子查询，该子查询将通过每个A获取max(b)，然后将该值重新连接到表中以返回剩余的列匹配子查询的值:

You will want to use a subquery that will get the max(b) by each A and then join that value back to your table to return the remaining columns that match the values of the subquery:

select *
from mytable t1
inner join
(
  select A, max(b) B
  from mytable
  where b >50
  group by a
) t2
  on t1.a = t2.a
  and t1.b = t2.b
where t1.b >50

请参见带演示的SQL小提琴

这篇关于SQL从聚合函数获取其他行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！