SQL:GROUP BY记录,然后从每个组中获取最后一条记录? [英] SQL: GROUP BY records and then get last record from each group?
本文介绍了SQL:GROUP BY记录,然后从每个组中获取最后一条记录?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的表:
id| name | attendence
1 | Naveed| 1
2 | Naveed| 1
3 | Adil | 1
4 | Adil | 1
我使用以下查询:
I use following query:
SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name
以上查询的结果:
The result with above query:
id| name | attendence
3 | Adil | 1
1 | Naveed | 1
问题:
以上结果组按名称排序,但显示每个组的第一行。我想从每个组中选择最后一行(按ID)。
Question:
Above result group rows by name but show first row from each group. I want to select last row(by id) from each group.
例如:
For example:
id| name | attendence
2 | Naveed | 1
4 | Adil | 1
如何为上述结果编写查询。
How to write query for above result.
感谢
Thanks
推荐答案
SELECT a.*
FROM test a
WHERE a.attendence = 1
AND NOT EXISTS (select 1 from test where name = a.name and id > a.id and attendence = 1)
GROUP BY name
可能不再需要该群组了。
Might not even need the group by anymore.
这篇关于SQL:GROUP BY记录,然后从每个组中获取最后一条记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文