SQL GROUP BY with aggregate

Question

如果我有一个包含数据的表Numbers：

+------+------+------+------+
| colA | colB | colC | colD |
+------+------+------+------+
|    1 |    2 |    3 |    4 |
|    1 |    2 |    9 |    5 |
+------+------+------+------+

并做到：

select colA, colB, colC, MAX(colD) FROM Numbers GROUP BY colA, colB;

我认为它应该返回row 2。它按colA, colB分组，并在colD中选择最大的一个。

不幸的是，这不起作用，因为您还必须按colC分组才能返回它。

为什么？有没有其他方法可以做我想做的事情？

我想要在colA和colB中相同但在colD中最大的行。

Answer 1

有几种方法可以处理这个问题。也许最简单的方法是对执行colA, colB分组的子查询执行JOIN，并从中找到完整的对应行。

SELECT 
  tbl.colA,
  tbl.colB,
  tbl.colC,
  tbl.colD
FROM tbl JOIN (
  SELECT
    colA,
    colB,
    MAX(colD) AS maxD
  FROM tbl
  GROUP BY colA, colB
) g ON tbl.colA = g.colA AND tbl.colB = g.colB AND tbl.colD = g.maxD

Answer 2

您可以这样做：

 SELECT N1.colA, N1.colB, N1.colC, N1.colD
   FROM Numbers N1
   LEFT JOIN Numbers N2 ON N2.colA = N1.colA
                       AND N2.colB = N1.colB
                       AND N2.colD > N1.colD
  WHERE N2.colA IS NULL;

左连接将在同一个表中查找具有相同列A和B以及更大列D的行。如果没有找到行，则在列D中具有最大值。

这在技术上与以下内容相同：

SELECT *
  FROM Numbers N
 WHERE NOT EXISTS
     ( SELECT NULL /* or whatever you want, doesn't matter */
         FROM Numbers
        WHERE colA = N.colA
          AND colB = N.colB
          AND colD > N.colD
     )

请注意，如果此操作返回重复行，则可能需要添加DISTINCT。

Answer 3

查询的问题之一是，您希望显示codC的值，但它不在group by part上。要在不使用聚集函数的情况下显示值，它必须位于group by part上。因此，要修复您的查询，您可以这样做：

select n1.* from Number n1
inner join (select colA, colB, max(colD) as colD from Number GROUP BY colA, colB) n2 on n1.colA = n2.colA and n1.colB = n2.colB and n1.colD = n2.colD

它将为每个colA和colB选择具有最大值(ColD)的所有行。