SQL在最后日期之前加入 [英] SQL joined by last date

问题描述

这是一个不止一个的问题，但是我找不到我想要的东西.我正在寻找联接两个表，其中联接表由按日期时间排序的最后一个寄存器设置，直到一切正常.

This is a question asked here before more than once, however I couldn't find what I was looking for. I am looking for join two tables, where the joined table is set by the last register ordered by date time, until here all is ok.

我的麻烦开始于联接表上有两个以上记录，让我为您展示一个示例

My trouble start on having more than two records on the joined table, let me show you a sample

table_a
-------
id
name
description
created
updated

table_b
-------
id
table_a_id
name
description
created
updated

我一开始所做的是:

SELECT a.id,  b.updated
FROM table_a AS a 
LEFT JOIN (SELECT table_a_id, max (updated) as updated 
           FROM table_b GROUP BY table_a_id ) AS b 
ON a.id = b.table_a_id

直到这里我得到cols，a.id和b.updated.我需要完整的table_b列，但是当我尝试向查询中添加新列时，Postgres告诉我需要将我的列添加到GROUP BY条件中才能完成查询，结果不是我在找.

Until here I was getting cols, a.id and b.updated. I need the full table_b cols, but when I try to add a new col to my query, Postgres tells me that I need to add my col to a GROUP BY criteria in order to complete the query, and the result is not what I am looking for.

我正在尝试找到一种包含此列表的方法.

I am trying to find a way to have this list.

推荐答案

DISTINCT ON 或是您的朋友.这是使用正确语法的解决方案:

DISTINCT ON or is your friend. Here is a solution with correct syntax:

SELECT a.id, b.updated, b.col1, b.col2
FROM   table_a as a
LEFT   JOIN (
   SELECT DISTINCT ON (table_a_id)
          table_a_id, updated, col1, col2
   FROM   table_b
   ORDER  BY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

或者，从table_b获取整行:

SELECT a.id, b.*
FROM   table_a as a
LEFT   JOIN (
   SELECT DISTINCT ON (table_a_id)
          *
   FROM   table_b
   ORDER  BY table_a_id, updated DESC
   ) b ON a.id = b.table_a_id;

此技术的详细说明以及与此密切相关的问题下的替代解决方案:
选择每个GROUP BY组中的第一行?

Detailed explanation for this technique as well as alternative solutions under this closely related question:
Select first row in each GROUP BY group?

这篇关于SQL在最后日期之前加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！