在多个条件下返回左外连接的最后一行

Question

我正在尝试查询两个表..我在下面制作了示例表：

---------------TABLE1 (t1)------------
| id  |type |    date    |
|  1  |  A  | 2018-01-01 |
|  2  |  C  | 2018-02-01 |
|  3  |  C  | 2018-03-01 |
|  4  |  C  | 2018-04-01 |
|  5  |  A  | 2018-01-01 |
|  6  |  C  | 2018-02-01 |
|  7  |  C  | 2018-03-01 |
|  8  |  B  | 2018-04-01 |
---------------TABLE2 (t2)------------
| id  |    date    |amount|  created   |
|  1  | 2018-01-01 |  100 | 2017-10-01 |
|  1  | 2018-01-01 |  700 | 2017-12-01 |
|  1  | 2018-03-01 |  300 | 2018-01-01 |
|  2  | 2018-02-01 |  500 | 2017-12-01 |
|  2  | 2018-02-01 |   50 | 2017-10-01 |
|  3  | 2018-03-01 |  750 | 2018-02-01 |
|  3  | 2018-03-01 | 1000 | 2018-03-01 |
|  4  | 2018-04-01 |  200 | 2018-03-01 |
|  5  | 2018-04-01 |  300 | 2018-02-01 |

我想要以下内容：

---------------TABLE3------------
| id  |type |    date    |amount|  created   |
|  2  |  C  | 2018-02-01 |  500 | 2017-12-01 |
|  3  |  C  | 2018-03-01 | 1000 | 2018-03-01 |
|  4  |  C  | 2018-04-01 |  200 | 2018-03-01 |
|  6  |  C  | 2018-02-01 | NULL |    NULL    |
|  7  |  C  | 2018-03-01 | NULL |    NULL    |

我想要一个左联接(我认为在Postgres中，这是一个外部联接(在t1.id = t2.id，t1.date=t2.date和t1.type = 'C'的条件下。

我很容易完成查询，但是AM有时会在t2上接受多个匹配。在这种情况下，我只想从t2获得最新created日期的行。

最后，我期望一张与以下行相同的行数。 SELECT COUNT(*) FROM t1 WHERE type = 'C'

我在网站上检查了其他多个问题，但无法在多个条件下弄清楚如何做。

Answer 1

demo：db＆lt;> fiddle

您可以首先用DISTINCT ON过滤T2：

SELECT DISTINCT ON (id)
    *
FROM
    t2
ORDER BY id, date DESC

之后，您可以加入此处：

SELECT *
FROM t1
LEFT JOIN (
   -- subquery here
) t2
ON t1.id = t2.date

DISTINCT ON给出了有序组的第一个记录。您的小组是id。如果您按降序订购了id组，则将最多的记录移至顶部。

之后，您可以在WHERE子句中通过type过滤。

Answer 2

Postgres的扩展名为distinct on。这使表达逻辑并每行id返回一排非常简单：

select distinct on (t1.id), t1.*, t2.amount, t2.created
from table1 t1 left join
     table2 t2
     on t1.id = t2.id and t1.date = t2.date
where t1.type = 'C'
order by t1.id, t2.created desc