Postgresql:从 2 个表创建视图，在同一列的最大值上加入

Question

我有 2 个表

Sys_Log

request_id   request_date
----------------------------
1            2022-01-01
2            2022-01-02

值

request_id   city      country
---------------------------------
1            Berlin    DE
1            Dortmund  DE
1            Dresden   DE
1            Paris     FR
1            London    EN
2            Dublin    IR
2            Bochum    DE
2            Essen     DE
2            Herne     DE

我正在尝试创建一个视图，其中仅显示最后一个请求(最多 request_id)中的数据。

理想的结果

request_id   city      country    request_date
----------------------------------------------
2            Dublin    IR         2022-01-02
2            Bochum    DE         2022-01-02
2            Essen     DE         2022-01-02
2            Herne     DE         2022-01-02

到目前为止，我做了一个请求 id 在两个表中都匹配的请求 ID

SELECT * FROM (SELECT DISTINCT city, 
country, 
sys_log.request_id,
sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
SELECT sys.request_date, 
sys.request_id
FROM public."Sys_Log" AS sys
) AS sys_log ON sys_log.request_id = VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

我不确定如何继续，max(request_id) 我收到错误。

SELECT * FROM (SELECT DISTINCT city, 
country, 
sys_log.request_id,
sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
SELECT sys.request_date, 
max(sys.request_id) as max_ri
FROM public."Sys_Log" AS sys
) AS sys_log ON sys_log.max_ri= VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

错误：

Spalte »sys.request_date« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden

像sys.request_date必须在 GROUP-BY 或 agg. 函数中使用一样。

Answer 1

如本文所述，您可以使用limit.因此，您的查询将如下所示：

SELECT * FROM (SELECT DISTINCT city, 
country, 
sys_log.request_id,
sys_log.request_date
FROM public."Values" AS VAL
LEFT JOIN (
SELECT sys.request_date, 
sys.request_id
FROM public."Sys_Log" ORDER BY sys.request_id DESC LIMIT 1 AS sys
) AS sys_log ON sys_log.request_id = VAL.request_id
WHERE VAL.request_id IS NOT NULL) AS VAL_View

Answer 2

你可以使用 RANK 函数，试试：

with cte as (
SELECT request_id,city,country
,RANK () OVER (ORDER BY request_id desc ) id_rank
FROM
values_tbl
) select cte.request_id,city,country,request_date 
from cte 
inner join sys_Log on cte.request_id=sys_Log.request_id
where cte.id_rank=1;

结果：

request_id    city    country request_date
2        Herne      DE   2022-01-02
2        Essen      DE   2022-01-02
2        Bochum     DE   2022-01-02
2        Dublin     IR   2022-01-02

演示

查看查询：

create view my_view as with cte as (
SELECT request_id,city,country
,RANK () OVER (ORDER BY request_id desc ) id_rank
FROM
values_tbl
) select cte.request_id,city,country,request_date 
from cte 
inner join sys_Log on cte.request_id=sys_Log.request_id
where cte.id_rank=1;

演示