我一直在处理一些数据库查询,在试图为获得聚合值的行获取主键时遇到了一些问题。我使用的是Postgresqlv10。
以下表为例:
instructionr_schedule_id | instructionr_name | 课程名称开始日期 | |
---|---|---|---|
1 | bob | 数据库2015-01-01 00:00:00.0000+00:00 | |
2 | bob | 数据库 | 2018-01-01 00:00:00.0000+00:00|
3 | bob | 数据库2024-01-01 00:00:00.000000+000:00 | |
4 | alice | 数据库2021-01-01 00:00:00.000000+000:00 | |
5个 | alice | 数据库2022-01-01 00:00:00.000000+000:00 |
感谢您的精确脚本,这很有帮助。
这里有一种方法可以实现你想要的目标。第一步是筛选出now((之前的所有日期(即忽略未来的日期(,然后我们可以根据可用的最新条目对它们进行排名,最后我们筛选出rnk=1的条目,它将提取最新的条目。
with data
as (
select *,row_number() over(partition by instructor_name,course_name order by start_date desc) as rnk
from instructor_schedule
where start_date<=now() --restrict records to entries which have occured in the past
)
select *
from data x
where x.rnk=1 --as we are ranking using the earliest start_date rnk=1, gets you the latest entry record.
在您的原始查询中,您可以完成您要查找的结果,如下所示。
select orig.*
from instructor_schedule orig
join (select max(start_date) as max_start_date,
course_name,
instructor_name
from instructor_schedule as is1
where start_date<=now()
group by course_name,
instructor_name
) inner1
on orig.course_name=inner1.course_name
and orig.instructor_name=inner1.instructor_name
and orig.start_date=inner1.max_start_date
示例证明
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=09b536a0495ab8786cc2cd0dbf8077ed
试试这个:
with main_tab as (
select *,
row_number() over (partition by instructor_name order by start_date desc) rn
from instructor_schedule
where start_date <= now()
)
select * from main_tab where rn = 1;
with data as (
select *,
row_number() over (partition by instructor_name order by start_date desc) as rn
from instructor_schedule
where start_date <= now()
)
select * from data where rn = 1;