blocks|key|1628200|text|这称为选择列的分组最大值。Here+are+several+different+approaches+for+mysql.|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1628201|下面是我会怎么做：|1628202|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|code-block|syntax|javascript|1628203|虽然mysql将在内存中为子查询创建一个临时表，但这将是相对有效的。我假设您已经在这个表的(id，version_id)上有一个索引。|1628204|对于这类问题，或多或少必须使用子查询(semi-joins是另一个例子)，这是SQL的一个缺点。|1628205|子查询在mysql中没有得到很好的优化，但不相关的子查询并不是那么糟糕，只要它们不是太大以至于它们会被写到磁盘上而不是内存中。假设在这个查询中只有两个int，那么在此之前的子查询可能有数百万行，但是第一个查询中的select+*子查询可能很快就会遇到这个问题。|1628206|entityMap|0|LINK|mutability|MUTABLE|url|http://kristiannielsen.livejournal.com/6745.html|1|http://en.wikipedia.org/wiki/Relational_algebra#Semijoin^0|D|1C|0|0|0|0|0|J|A|1|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|O|3|P|5|6|7|1B|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]]]

It's called selecting the group-wise maximum of a column. <a href="http://kristiannielsen.livejournal.com/6745.html" rel="noreferrer">Here are several different approaches for mysql.</a>

Here's how I would do it:

<pre><code>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
</code></pre>

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 (<a href="http://en.wikipedia.org/wiki/Relational_algebra#Semijoin" rel="noreferrer">semi-joins</a> 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.

blocks|key|1628243|text|我想这样就可以了，不过不确定是最好的还是最快的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1628244|SELECT+*+FROM+table+
WHERE+(id,+version_id)+IN+
++(SELECT+id,+MAX(version_id)+FROM+table+GROUP+BY+id)|code-block|syntax|javascript|1628245|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think this would do it, not sure if it is the best or fastest though.

<pre><code>SELECT * FROM table 
WHERE (id, version_id) IN 
 (SELECT id, MAX(version_id) FROM table GROUP BY id)
</code></pre>

blocks|key|1628277|text|这是伪代码，但这样的代码应该可以正常工作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1628278|select+*
from+table
inner+join
(
++++select+id+,+max(version_id)+maxVersion
++++from+table+
)+dvtbl+ON+id+=+dvtbl.id+&&+versionid+=+dvtbl.maxVersion|code-block|syntax|javascript|1628279|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is pseudo code but something like this should work just fine

<pre><code>select *
from table
inner join
(
 select id , max(version_id) maxVersion
 from table 
) dvtbl ON id = dvtbl.id &amp;&amp; versionid = dvtbl.maxVersion
</code></pre>

blocks|key|1413646|text|我通常使用子查询来执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1413647|select+id，version_id，field1，field2+from+datatable+dt+where+id+=+(select+id+from+datatable+id+=+dt.id+order+by+version_id+desc+limit+1)|1413648|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I usually do this with a subquery:

select id, version_id, field1, field2 from datatable as dt where id = (select id from datatable where id = dt.id order by version_id desc limit 1)

blocks|key|1413691|text|SELECT+id,+version_id,+field1,+field2
FROM+(
++++SELECT+@prev+=+id+AS+st,+(@prev+:=+id),+m.*
++++FROM+(
+++++++++++(SELECT+@prev+:=+NULL)+p,
+++++++++++(
++++++++++++SELECT+*
++++++++++++FROM+++mytable
++++++++++++ORDER+BY
+++++++++++++++++++id+DESC,+version_id+DESC
+++++++++++)+m
+++++)+m2
WHERE+NOT+IFNULL(st,+FALSE);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1413692|无子查询，如果你有一个UNIQUE+INDEX+ON+MYTABLE+(id,+version_id)+(我认为你应该这样做)，只需传递一次|unstyled|offset|length|style|CODE|1413693|entityMap^0|0|B|14|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT id, version_id, field1, field2
FROM (
 SELECT @prev = id AS st, (@prev := id), m.*
 FROM (
 (SELECT @prev := NULL) p,
 (
 SELECT *
 FROM mytable
 ORDER BY
 id DESC, version_id DESC
 ) m
 ) m2
WHERE NOT IFNULL(st, FALSE);
</code></pre>

No subqueries, one pass on <code>UNIQUE INDEX ON MYTABLE (id, version_id)</code> if you have one (which I think you should)

blocks|key|1628482|text|没有经过测试，但像这样的东西可能会起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1628483|SELECT+*+FROM+table+GROUP+BY+id+ORDER+BY+MAX(version_id)+DESC|1628484|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

not tested it but something like this might work:

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

blocks|key|1413814|text|此查询将在没有group+by的情况下执行此工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1413815|SELECT+*+FROM+table+AS+t
LEFT+JOIN+table+AS+t2+
++++ON+t.id=t2.id+
++++AND+t.version_id+<+t2.version_id
WHERE+t2.id+IS+NULL|code-block|syntax|javascript|1413816|它不需要任何临时表。|1413817|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This query will do the job without a group by:

<pre><code>SELECT * FROM table AS t
LEFT JOIN table AS t2 
 ON t.id=t2.id 
 AND t.version_id &lt; t2.version_id
WHERE t2.id IS NULL
</code></pre>

It does not need any temporary tables.

blocks|key|1413879|text|你也可以选择解析函数，这会给你更多的控制|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1413880|select+tmp.*+from+(+select+id,version_id,field1,field2,+rank()+over(partition+by+id+order+by+version_id+desc+)+as+rnk+from+table)+tmp+where+tmp.rnk=1|offset|length|style|CODE|1413881|如果您遇到依赖于数据类型的rank()函数的问题，那么也可以从row_number()或dense_rank()中进行选择。|1413882|entityMap^0|0|0|45|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

One can always go for analytical functions as well which will give you more control 

<code>select tmp.* from ( select id,version_id,field1,field2, rank() over(partition by id order by version_id desc ) as rnk from table) tmp where tmp.rnk=1</code>

If you face issue with rank() function depending on the type of data then one can choose from row_number() or dense_rank() too.

I have a database table like this:

<pre><code>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
</code></pre>

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:

<pre><code>SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id
</code></pre>

MySQL - Control which row is returned by a group by

数据库

我有一个数据库表，如下所示：id    version_id    field1    field21     1             texta      text11     2             textb      text22     1             textc      text32 ...

问MySQL -控制group by返回的行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -控制group by返回的行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -控制group by返回的行
EN