MySQL GROUP BY和HAVING

Question

我正在按列X分组我的结果，并且想要返回在每个分组中具有最高的列Y值的行。

SELECT * 
FROM   mytable 
GROUP  BY col1 
HAVING col2 >= (SELECT MAX(col2) 
                FROM   mytable AS mytable2 
                WHERE  mytable2.col1 = mytable.col1 GROUP BY mytable2.col1) 

我想优化上面的查询。是否可以在不使用子查询的情况下完成？

我找到了解决方案，它比你想象的简单：

SELECT * FROM (SELECT * FROM mytable ORDER BY col2 DESC) temp GROUP BY col1

在20,000行数据上，运行时间为5毫秒。

- Elie

---

你认为子查询不够优化吗？如果有另一种表达方式，你认为数据库引擎会如何执行这个请求？ - harpo

@harpo说得没错，但这个例子是一个相关子查询。 - OMG Ponies

Answer 1

使用派生表/内联视图进行JOIN：

SELECT x.* 
  FROM mytable x
  JOIN (SELECT t.col1,
               MAX(t.col2) AS max_col2
          FROM MYTABLE t
      GROUP BY t.col1) y ON y.col1 = x.col1
                        AND y.max_col2 >= x.col2

请注意，如果存在多个相关的y记录，则会重复复制x记录。 要删除重复项，请使用DISTINCT：

SELECT DISTINCT x.* 
  FROM mytable x
  JOIN (SELECT t.col1,
               MAX(t.col2) AS max_col2
          FROM MYTABLE t
      GROUP BY t.col1) y ON y.col1 = x.col1
                        AND y.max_col2 >= x.col2

以下代码未经测试，但不会返回重复项（假设数据有效）:

SELECT x.* 
  FROM mytable x
 WHERE EXISTS (SELECT NULL
                 FROM MYTABLE y
                WHERE y.col1 = x.col1
             GROUP BY y.col1
               HAVING MAX(y.col2) >= x.col2)

- OMG Ponies

---

第一个查询不对吗？我认为内联视图只有一行（因此不完全加入到“x”）。即使这样，如果您按col1分组，它也只会具有每个特定行的col1的最大值。 - Matt

第一个问题是返回重复项（无用），第二个问题是运行时间太长（在20,000行上需要3秒）。 - Elie

@Elie：你给我点了踩吗？我没有你的表格或数据来测试速度。索引表格取决于你。 - OMG Ponies

@OMG Ponies - 是的，我误解了问题。我以为 @Elie 正试图提取所有行，这些行的 col2 等于整个 table 的 MAX(col2)。 - Matt

@OMG Ponies：我没有给你点踩，但也没有给你点赞……我提供的查询仍然比你的运行速度快（大约半秒）。我仍在寻找更好的优化方法（顺便感谢你的建议）。 - Elie

Answer 2

您的Col2永远不会大于MAX(col2)，因此我建议使用col2 = MAX(col2)

所以这里是查询

SELECT * FROM  mytable GROUP BY col1 HAVING  col2 = MAX(  col2 ) 

- Fisherman