时间戳

首页 > 解决方案 > 当组在另一个表上时，使用左连接技巧检索组的最大/最小行

mysql - 当组在另一个表上时，使用左连接技巧检索组的最大/最小行

问题描述

我正在尝试将左连接技巧应用于下表。这个技巧在这里有很好的记录，涉及在比较您需要的最小值或最大值的字段上进行左自连接，然后左连接为最小或最大行生成空值，然后您选择该空值匹配。但是，当组字段位于另一个表上时，我遇到了解决此问题的问题。下面的表格由 messjoin.fk_mess = message.id 连接，我在查询中包含了我的最佳尝试。它目前无法进行分组。

这是当组与最小/最大字段在同一个表上时的小提琴示例

CREATE TABLE messages(`id` int, `when` date);

CREATE TABLE messjoin(`grp` int, `fk_mess` int);

INSERT INTO messages
    (`id`, `when`)
VALUES
    (1,'2000-08-14'),
    (2,'2000-08-15'),
    (3,'2000-08-16'),
    (4,'2000-08-17'),
    (5,'2000-08-18'),
    (6,'2000-08-19');
    
INSERT INTO messjoin
    (`grp`, `fk_mess`)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 4),
    (2, 5),
    (2, 6);


select p1.*, m1.*, m2.*
      from messjoin p1 
inner join messages m1 on p1.fk_mess = m1.id
inner join messjoin p2 on p2.fk_mess = m1.id
left  join messages m2 on p2.grp = p1.grp and m1.when < m2.when
where m2.id is null;
+------+---------+------+------------+------+------+
| grp  | fk_mess | id   | when       | id   | when |
+------+---------+------+------------+------+------+
|    2 |       6 |    6 | 2000-08-19 | NULL | NULL |
+------+---------+------+------------+------+------+

我想要的是为每组 .grp 生成最大日期，如下所示：

+------+---------+------+------------+------+------+
| grp  | fk_mess | id   | when       | id   | when |
+------+---------+------+------------+------+------+
|    1 |       3 |    3 | 2000-08-16 | NULL | NULL |
|    2 |       6 |    6 | 2000-08-19 | NULL | NULL |
+------+---------+------+------------+------+------+

我不想要聚合函数或子查询解决方案！这是在mysql中

谢谢！

标签： mysqlgreatest-n-per-group

解决方案

要完成这项工作，您需要做LEFT JOIN的JOIN是两个表的相互连接：

SELECT p1.*, m1.*, m2.*
FROM (messparent p1 JOIN messages m1 ON p1.fk_mess = m1.id)
LEFT JOIN
(messparent p2 JOIN messages m2 ON p2.fk_mess = m2.id)
ON m2.when > m1.when AND p2.grp = p1.grp
WHERE m2.id IS NULL

输出：

grp fk_mess id  when        id  when
1   3       3   2000-08-16  (null)  (null)
2   6       6   2000-08-19  (null)  (null)

SQLFiddle。

推荐阅读