oracle 选择最近的条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40435326/
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
select the most recent entry
提问by Tsiftis Karampouzouklis
I have the following table:
我有下表：
LOCATION_ID, PERSON_ID, DATE
3, 65, 2016-06-03
7, 23, 2016-10-28
3, 23, 2016-08-05
5, 65, 2016-07-14
I want to build a select query in PL/SQL to select the records with the most recentlocation_id per person_id. For the above sample, the desired result should be:
我想在 PL/SQL 中构建一个选择查询来选择每个 person_id具有最新location_id的记录。对于上面的示例，所需的结果应该是：
LOCATION_ID, PERSON_ID, DATE
5, 65, 2016-07-14
7, 23, 2016-10-28
(DATE expressed as 'YYYY-MM-DD')
（日期表示为'YYYY-MM-DD'）
Thank you!
谢谢！
回答by Wernfried Domscheit
The other proposals are correct but the most compact and fastest solution is most likely when you use FIRST_VALUE and LAST_VALUE Analytic Functions
其他建议是正确的，但是当您使用FIRST_VALUE 和 LAST_VALUE 分析函数时最有可能是最紧凑和最快的解决方案
SELECT DISTINCT
FIRST_VALUE(LOCATION_ID) OVER (PARTITION BY PERSON_ID ORDER BY THE_DATE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LOCATION_ID,
PERSON_ID,
MAX(THE_DATE) OVER (PARTITION BY PERSON_ID) AS LAST_DATE
FROM YOUR_TABLE;
Other people prefer
其他人更喜欢
SELECT
MAX(LOCATION_ID) KEEP (DENSE_RANK FIRST ORDER BY DATE) as LOCATION,
PERSON_ID,
MAX(DATE) as LAST_DATE
FROM YOUR_TABLE
GROUP BY PERSON_ID;
which does the same, but I am not so familiar with this clause. See aggregate_function KEEP
它的作用相同，但我对这个条款不太熟悉。见aggregate_function KEEP
回答by quasoft
You can first extract the most recent event for each person by grouping results by PERSON_IDand selecting the MAX(DATE).
您可以首先通过将结果分组PERSON_ID并选择MAX(DATE).
Then join the table with itself on those two columns to retrieve the LOCATION_ID
然后在这两列上将表与自身连接以检索 LOCATION_ID
SELECT
YOUR_TABLE.LOCATION_ID,
YOUR_TABLE.PERSON_ID,
YOUR_TABLE.DATE
FROM
(SELECT
PERSON_ID, MAX(DATE) AS max_date
FROM
YOUR_TABLE
GROUP BY
PERSON_ID
) AS t1
LEFT JOIN
YOUR_TABLE
ON
YOUR_TABLE.PERSON_ID = t1.PERSON_ID
AND
YOUR_TABLE.DATE = t1.max_date
By the way, you should not use reserved words like DATEfor column names.
顺便说一句，您不应该使用保留字，例如DATE列名。
Here is fiddle to show it working: http://sqlfiddle.com/#!9/efdcb/2
这是显示它工作的小提琴：http: //sqlfiddle.com/#!9/efdcb/2
回答by Hawk
@quasoft is correct. Another way to deal with these kinds of GROUP BYproblems (when you want to return more column than what you want to group by. In your case, you need to return location_id, person_id. But you only need to group by person_id), is to use analytical functions.
@quasoft 是正确的。处理此类GROUP BY问题的另一种方法（当您想要返回的列多于您想要分组的列时。在您的情况下，您需要返回 location_id、person_id。但您只需要按 person_id 分组），是使用分析功能。
--schema:
CREATE TABLE my_table
(
location_id NUMBER,
person_id NUMBER,
date_ DATE
);
INSERT ALL
INTO my_table
VALUES (3, 65, To_date('2016-06-03', 'YYYY-MM-DD'))
INTO my_table
VALUES (7, 23, To_date('2016-10-28', 'YYYY-MM-DD'))
INTO my_table
VALUES (3, 23, To_date('2016-08-05', 'YYYY-MM-DD'))
INTO my_table
VALUES (5, 65, To_date('2016-07-14', 'YYYY-MM-DD'))
SELECT *
FROM dual;
--query:
WITH ordered
AS (SELECT location_id,
person_id,
date_,
Row_number()
over (
PARTITION BY person_id
ORDER BY date_ DESC) RN
FROM my_table)
SELECT location_id,
person_id,
date_
FROM ordered
WHERE rn = 1;
The query orderedsort your rows for each group by date. The main query, returns the first 1 of each group after it is sorted. Hence, it will return the in this case the last one (we ordered by date_ desc).
该查询ordered按日期对每个组的行进行排序。主查询，在排序后返回每个组的前 1 个。因此，在这种情况下，它将返回最后一个（我们按 date_desc 排序）。
回答by Ocean
This might work!
这可能有效！
SELECT * FROM Your_Table A
JOIN (SELECT PERSON_ID,MAX(DATE) as MaxDate FROM Your_Table
GROUP BY PERSON_ID) B
ON A.PERSON_ID = B.PERSON_ID AND A.DATE = B.MaxDate
