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

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    |

我想要一个 LEFT JOIN(我认为在 Postgres 中这是默认的外连接)条件是 t1.id = t2.id, t1.date=t2.date 和 t1.type = 'C'。

我很容易完成查询，但偶尔会在 t2 上收到多个匹配项。在这种情况下，我只想从 t2 中获取具有最新 created 日期的行。

最后，我希望表格的行数与以下内容相同:SELECT COUNT(*) FROM t1 WHERE type = 'C'

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

Answer 1

demo:db<>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 进行过滤。