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

Question

我有两张桌子

系统日志

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

我正在尝试创建一个视图，其中仅显示来自上一个请求（max request\u 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的例子

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

我不知道如何继续，最大（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 erschinen oder in einer Aggregatfunktion verwender t werden

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

Answer 1

您可以使用秩函数，尝试：

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;

演示

Answer 2

如本文所述，您可以使用限制。因此，您的查询如下所示：

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