mysql - Sql 自连接以及不匹配的行

Question

我有一张这样的桌子

id | user | name | action | created_at
--------------------------------------------------
1  | 42    | eve | open   | 2020-01-06 06:17:42
2  | 42    | eve | close  | 2020-01-06 06:27:42
3  | 42    | eve | open   | 2020-01-06 06:37:42
4  | 42    | eve | close  | 2020-01-06 06:47:42
5  | 42    | eve | open   | 2020-01-06 06:57:42

我需要得到这张桌子：

user | name | open | open_created_at     | close | close _created_at 
-----------------------------------------------------------------------
42   | eve  | open | 2020-01-06 06:17:42 | close | 2020-01-06 06:27:42
42   | eve  | open | 2020-01-06 06:37:42 | close | 2020-01-06 06:47:42
42   | eve  | open | 2020-01-06 06:57:42 | null  | null

这是我得到的表：

SELECT t1.user, t1.name, t1.action, t1.created_at, t2.action, t2.created_at 
FROM tabel t1, table t2
WHERE t1.user = t2.user AND t1.action = 'open' AND t2.action = 'close' AND t1.created_at < t2.created_at
GROUP BY t1.id


user | name | open | open_created_at     | close | close _created_at 
-----------------------------------------------------------------------
42   | eve  | open | 2020-01-06 06:17:42 | close | 2020-01-06 06:27:42
42   | eve  | open | 2020-01-06 06:37:42 | close | 2020-01-06 06:47:42

如何在同一列中获得具有匹配打开/关闭的表以及没有匹配关闭列的行？

标签： mysqlsqljoinself-join

Answer 1

您需要使用 aLEFT JOIN而不是 an INNER JOIN，并且需要将WHERE子句移入JOIN条件中。您还应该在t2值周围添加聚合函数以确保结果一致：

SELECT t1.user, t1.name, t1.action AS open, t1.created_at AS open_created_at,
       MIN(t2.action) AS close, MIN(t2.created_at) AS close_created_at
FROM log t1
LEFT JOIN log t2 ON t1.user = t2.user AND t2.action = 'close' AND t1.created_at < t2.created_at
WHERE t1.action = 'open'
GROUP BY t1.id, t1.name, t1.action, t1.created_at

输出：

user    name    open    open_created_at         close   close_created_at
42      eve     open    2020-01-06 06:17:42     close   2020-01-06 06:27:42
42      eve     open    2020-01-06 06:37:42     close   2020-01-06 06:47:42
42      eve     open    2020-01-06 06:57:42     (null)  (null)

SQLFiddle 上的演示