SQL 在分组查询时，怎么获取最新一条记录

[ackfin01]

表 A 和表 B，为 1 对多关系，如何查询对于某个 A，多个 B 中最近更新的那一条记录

[gzq527]

每组最新的那一条？

[ackfin01]

@gzq527 嗯 有个字段纪录了更新时间

[gzq527]

https://blog.csdn.net/u012660464/article/details/78605078
了解一下

[watzds]

https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

[loongwang]

SELECT B.*
FROM B b1 JOIN
(select b2.x,max(b.date)
FROM B b2 JOIN A ON b2.x=A.x

[loongwang]

这样行吗,看着有点丑
SELECT B.*
FROM B b1 JOIN
(
select A.x,max(b.date)
FROM B b2 JOIN A ON b2.x=A.x
GROUP BY A.x
) C ON b1.x=C.x where b1.date=C.date

[ackfin01]

@gzq527
这个只 GROUP BY b.USER_ID; 还可以 SELECT ID,USER_ID,problems,last_updated_date ？

[ackfin01]

@loongwang em...应该可以，不过感觉用 date 做比较条件，也行吧

[killaboy712]

先把 B 表分组排序，row_number() over(partition by xx order by 时间) as 'rk',然后 select * from B 排序后的表，where rk =1，再将其与 A 表关联

[fanhaipeng0403]

https://www.v2ex.com/t/442595#reply1

[fanhaipeng0403]

查询返回每个 id 的前 5

SELECT yourtable.* FROM yourtable INNER JOIN ( SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id) group_max ON yourtable.id = group_max.id AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5 ORDER BY yourtable.id, yourtable.year DESC

[ackfin01]

@watzds
@killaboy712
学到了～ Thx!

[lueffy]

mark 一下

[Magic347]

@killaboy712 漂亮，这里需要用到开窗函数，分组之后的 topk 问题的典型用例