Oracle 选择最近的日期记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11128194/
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
Oracle select most recent date record
提问by user1440675
I am trying to find the most recent record based on a date field. When I set latest = 1 in the where clause, I get an error. Please help if possible. DATE is a the field I'm sorting by. I have tried both latest = 1 and latest = '1'
我正在尝试根据日期字段查找最新记录。当我在 where 子句中设置 latest = 1 时，出现错误。如果可能，请帮忙。DATE 是我排序的字段。我已经尝试过 latest = 1 和 latest = '1'
SELECT
STAFF_ID,
SITE_ID,
PAY_LEVEL,
ROW_NUMBER() OVER (PARTITION BY STAFF_ID ORDER BY DATE DESC) latest
FROM OWNER.TABLE
WHERE END_ENROLLMENT_DATE is null
AND latest = 1
回答by Aprillion
you can't use aliases from select list inside the WHERE clause (because of the Order of Evaluationof a SELECT statement)
您不能在 WHERE 子句中使用来自选择列表的别名（因为SELECT 语句的求值顺序）
also you cannot use OVERclause inside WHERE clause - "You can specify analytic functions with this clause in the select list or ORDER BY clause." (citation from docs.oracle.com)
也不能OVER在 WHERE 子句中使用子句 - “您可以在选择列表或 ORDER BY 子句中使用此子句指定分析函数。” （引自docs.oracle.com）
select *
from (select
staff_id, site_id, pay_level, date,
max(date) over (partition by staff_id) max_date
from owner.table
where end_enrollment_date is null
)
where date = max_date
回答by Glenn
Assuming staff_id + date form a uk, this is another method:
假设 staff_id + date 形成一个 uk，这是另一种方法：
SELECT STAFF_ID, SITE_ID, PAY_LEVEL
FROM TABLE t
WHERE END_ENROLLMENT_DATE is null
AND DATE = (SELECT MAX(DATE)
FROM TABLE
WHERE staff_id = t.staff_id
AND DATE <= SYSDATE)
回答by Randy
i think i'd try with MAX something like this:
我想我会尝试使用 MAX 这样的东西：
SELECT staff_id, max( date ) from owner.table group by staff_id
then link in your other columns:
然后链接到您的其他列：
select staff_id, site_id, pay_level, latest
from owner.table,
( SELECT staff_id, max( date ) latest from owner.table group by staff_id ) m
where m.staff_id = staff_id
and m.latest = date
回答by user3244647
select *
from (select
staff_id, site_id, pay_level, date,
rank() over (partition by staff_id order by date desc) r
from owner.table
where end_enrollment_date is null
)
where r = 1
