返回 MySQL 中每个“group by”的“最后”行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9192673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning the 'last' row of each 'group by' in MySQL
提问by fadedbee
Is there a more efficient way of doing the following?
有没有更有效的方法来执行以下操作？
select *
from foo as a
where a.id = (select max(id) from foo where uid = a.uid group by uid)
group by uid;
)
This answer looks similar, but is this answer the best way of doing this - How to select the first row for each group in MySQL?
这个答案看起来很相似，但这个答案是最好的方法吗- 如何在 MySQL 中为每个组选择第一行？
Thanks,
谢谢，
Chris.
克里斯。
P.S. the table looks like:
PS表看起来像：
CREATE TABLE foo (
id INT(10) NOT NULL AUTO_INCREMENT,
uid INT(10) NOT NULL,
value VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`),
INDEX `uid` (`uid`)
)
data:
数据：
id, uid, value
1, 1, hello
2, 2, cheese
3, 2, pickle
4, 1, world
results:
结果：
id, uid, value
3, 2, pickle
4, 1, world
See http://www.barricane.com/2012/02/08/mysql-select-last-matching-row.htmlfor more details.
有关更多详细信息，请参阅http://www.barricane.com/2012/02/08/mysql-select-last-matching-row.html。
回答by Devart
回答by Swapnil Kumbhar
Returning the last row of each GROUP BYin MySQL with WHEREclause:
GROUP BY使用WHERE子句返回MySQL中每个的最后一行：
SELECT *
FROM foo
WHERE id IN (
SELECT Max(id)
FROM foo
WHERE value='XYZ'
GROUP BY u_id
)
LIMIT 0,30
回答by Swapnil Kumbhar
if table is big in size. Make viewcontaining all last row id
如果桌子很大。制作view包含所有最后一行 id
create view lastrecords as (select max(id) from foo where uid = a.uid group by uid)
Now join your main query with this view. It will be faster.
现在用这个加入你的主要查询view。它会更快。
SELECT t1.* FROM tablename as t1
JOIN lastrecords as t2
ON t1.id = t2.id AND t1.uid = t2.uid;
OR You can do join with last records direct in query also:
或者您也可以直接在查询中加入最后一条记录：
SELECT t1.* FROM tablename as t1
JOIN (SELECT uid, MAX(id) id FROM tablename GROUP BY id) as t2
ON t1.id = t2.id AND t1.uid = t2.uid;
回答by Ahmet Ozen
This has worked for me thanks!
这对我有用，谢谢！
SELECT t1.* FROM foo t1
JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
ON t1.id = t2.id AND t1.uid = t2.uid;
my version:
我的版本：
SELECT * FROM messages t1 JOIN (SELECT MAX(id) id FROM messages where uid = 8279 and actv=1 GROUP BY uid ) t2 ON t1.id = t2.id ORDER BY datex desc LIMIT 0,10;
this code below doesn't return all rows I want because if max id column is an inactive one then it skips the group even if the group has active rows..
下面的代码不会返回我想要的所有行，因为如果 max id 列是非活动列，那么即使该组具有活动行，它也会跳过该组。
select * from messages where uid = 8279 and actv=1 and id In (Select max(id) From messages Group By uid) order by datex desc;
回答by Vahid Najafi
The easiest way : you can select from selected list that is sorted.
最简单的方法：您可以从已排序的选定列表中进行选择。
SELECT * FROM (SELECT * FROM foo order by id DESC) AS footbl
group by uid
order by id DESC
回答by Tahajuda Mandariansah
This code works on me:
这段代码适用于我：
SELECT * FROM foo GROUP BY foo.uid
HAVING MAX(foo.id)
