MySQL - 控制一个组由哪个行返回 [英] MySQL - Control which row is returned by a group by

问题描述

  id version_id field1 field2 
 1 1 texta text1 
 1 2 textb text2 
 2 1 textc text3 
 2 2 textd text4 
 2 3 texte text5 
  

如果您没有完成这项工作，它会包含一些行的版本，然后是一些文本数据。

我想查询它并返回每个ID最高的版本。 （所以第二行和最后一行只在上面）。

我已经尝试使用group by by order by version_id DESC - 但它似乎按照它的顺序排序，所以这是行不通的。

任何人有任何想法？我不敢相信这是不可能完成的！

更新：

想出这个，工作，但使用子查询：

$ $ p $ $ $
$ b GROUP BY t1.id

解决方案

列的组合最大值。 这里有几种不同的mysql方法。

以下是我的做法：

  SELECT * 
 FROM（SELECT id，max（version_id）as version_id FROM表GROUP BY id）t1 
 INNER JOIN表t2 t2.id = t1.id和t1.version_id = t2.version_id 
  

这将会相对高效，不过mysql会在子查询的内存中创建一个临时表。我假设你已经有了这个表的一个索引（id，version_id）。

这是SQL的一个缺陷，你或多或少必须使用这种类型的子查询（半连接是另一个例子）。

子查询在mysql中没有很好的优化，但是不相关的子查询并不是很糟糕，只要它们不是很大以至于它们会被写入磁盘而不是内存。鉴于在此查询中只有两个整数，子查询可能会在发生数百万行之前发生，但第一个查询中的select *子查询可能很快就会遭受这个问题。

I have a database table like this:

id    version_id    field1    field2
1     1             texta      text1
1     2             textb      text2
2     1             textc      text3
2     2             textd      text4
2     3             texte      text5

If you didn't work it out, it contains a number of versions of a row, and then some text data.

I want to query it and return the version with the highest number for each id. (so the second and last rows only in the above).

I've tried using group by whilst ordering by version_id DESC - but it seems to order after its grouped, so this doesn't work.

Anyone got any ideas? I can't believe it can't be done!

UPDATE:

Come up with this, which works, but uses a subquery:

SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id

解决方案

It's called selecting the group-wise maximum of a column. Here are several different approaches for mysql.

Here's how I would do it:

SELECT *
FROM (SELECT id, max(version_id) as version_id FROM table GROUP BY id) t1
INNER JOIN table t2 on t2.id=t1.id and t1.version_id=t2.version_id

This will be relatively efficient, though mysql will create a temporary table in memory for the subquery. I assume you already have an index on (id, version_id) for this table.

It's a deficiency in SQL that you more or less have to use a subquery for this type of problem (semi-joins are another example).

Subqueries are not well optimized in mysql but uncorrelated subqueries aren't so bad as long as they aren't so enormous that they will get written to disk rather than memory. Given that in this query only has two ints the subquery could be millions of rows long before that happened but the select * subquery in your first query could suffer from this problem much sooner.

这篇关于MySQL - 控制一个组由哪个行返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！