每个按群组获取最新记录(Get the latest records per Group By SQL)
我有下表:
----------------------------------------------------------- ID oDate oName oItem oQty oRemarks ----------------------------------------------------------- 1 2016-01-01 A 001 2 2 2016-01-01 A 002 1 test 3 2016-01-01 B 001 3 4 2016-01-02 B 001 2 5 2016-01-02 C 001 2 6 2016-01-03 B 002 1 7 2016-01-03 B 001 4 ff.我想获得每个名字的最新记录。 所以结果应该是这样的:
----------------------------------------------------------- oDate oName oItem oQty oRemarks ----------------------------------------------------------- 2016-01-01 A 001 2 2016-01-01 A 002 1 test 2016-01-02 C 001 2 2016-01-03 B 002 1 2016-01-03 B 001 4 ff.有谁知道如何得到这个结果? 谢谢。
I have the following table:
----------------------------------------------------------- ID oDate oName oItem oQty oRemarks ----------------------------------------------------------- 1 2016-01-01 A 001 2 2 2016-01-01 A 002 1 test 3 2016-01-01 B 001 3 4 2016-01-02 B 001 2 5 2016-01-02 C 001 2 6 2016-01-03 B 002 1 7 2016-01-03 B 001 4 ff.I want to get the latest record for each name. So the result should be like this:
----------------------------------------------------------- oDate oName oItem oQty oRemarks ----------------------------------------------------------- 2016-01-01 A 001 2 2016-01-01 A 002 1 test 2016-01-02 C 001 2 2016-01-03 B 002 1 2016-01-03 B 001 4 ff.Does anyone know how to do get this result? Thank you.
最满意答案
rank窗口子句允许您根据某些分区对行进行排名,然后您可以选择最上面的那些:
SELECT oDate, oName, oItem, oQty, oRemarks FORM (SELECT oDate, oName, oItem, oQty, oRemarks, RANK() OVER (PARTITION BY oName ORDER BY oDate DESC) AS rk FROM my_table) t WHERE rk = 1The rank window clause allows you to, well, rank rows according to some partitioning, and then you could just select the top ones:
SELECT oDate, oName, oItem, oQty, oRemarks FROM (SELECT oDate, oName, oItem, oQty, oRemarks, RANK() OVER (PARTITION BY oName ORDER BY oDate DESC) AS rk FROM my_table) t WHERE rk = 1更多推荐
发布评论