mysql - MySQL-来自右表的连接行上的条件

我有两张桌子：

mysql> select * from orders;
+------+---------------------+------------+---------+
| id   | created_at          | foreign_id | data    |
+------+---------------------+------------+---------+
|    1 | 2010-10-10 10:10:10 |          3 | order 1 |
|    4 | 2010-10-10 00:00:00 |          6 | order 4 |
|    5 | 2010-10-10 00:00:00 |          7 | order 5 |
+------+---------------------+------------+---------+

mysql> select * from activities;
+------+---------------------+------------+------+
| id   | created_at          | foreign_id | verb |
+------+---------------------+------------+------+
|    1 | 2010-10-10 10:10:10 |          3 | get  |
|    2 | 2010-10-10 10:10:15 |          3 | set  |
|    3 | 2010-10-10 10:10:20 |          3 | put  |
|    4 | 2010-10-10 00:00:00 |          6 | get  |
|    5 | 2010-10-11 00:00:00 |          6 | set  |
|    6 | 2010-10-12 00:00:00 |          6 | put  |
+------+---------------------+------------+------+

现在我需要加入activities > orders on foreign_id列：对于每个顺序只选择一个活动（如果存在），以便ABS(TIMESTAMPDIFF(SECOND, orders.created_at, activities.created_at))最小。例如，顺序和活动大约在同一时间被创建。

+----------+---------+---------------------+-------------+------+---------------------+
| order_id | data    | order_created_at    | activity_id | verb | activity_created_at |
+----------+---------+---------------------+-------------+------+---------------------+
|        1 | order 1 | 2010-10-10 10:10:10 |           1 | get  | 2010-10-10 10:10:10 |
|        4 | order 4 | 2010-10-10 00:00:00 |           4 | get  | 2010-10-10 00:00:00 |
|        5 | order 5 | 2010-10-10 00:00:00 |        NULL | NULL | NULL                |
+----------+---------+---------------------+-------------+------+---------------------+

以下查询生成一组包含所需行的行。如果包含GROUP BY语句，则无法控制activities中的哪一行被联接。

SELECT o.id AS order_id
     , o.data AS data
     , o.created_at AS order_created_at
     , a.id AS activity_id
     , a.verb AS verb
     , a.created_at AS activity_created_at
FROM orders AS o
LEFT JOIN activities AS a ON a.foreign_id = o.foreign_id;

是否可以编写这样的查询？理想情况下，我想避免使用group by，因为这个部分是更大的报表查询的一部分。

最佳答案

因为这两个表都引用了一些神秘的外键，所以下面的查询可能会出错，但它可能会给您一个原则，您可以根据自己的目的进行调整。。。

DROP TABLE IF EXISTS orders;

CREATE TABLE orders
(id INT NOT NULL PRIMARY KEY
,created_at DATETIME NOT NULL
,foreign_id INT NOT NULL
,data    VARCHAR(20) NOT NULL
);

INSERT INTO orders VALUES
(1 ,'2010-10-10 10:10:10',3 ,'order 1'),
(4 ,'2010-10-10 00:00:00',6 ,'order 4'),
(5 ,'2010-10-10 00:00:00',7 ,'order 5');

DROP TABLE IF EXISTS activities;

CREATE TABLE activities
(id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,created_at          DATETIME NOT NULL
,foreign_id INT NOT NULL
,verb VARCHAR(20) NOT NULL
);

INSERT INTO activities VALUES
(1,'2010-10-10 10:10:10',3,'get'),
(2,'2010-10-10 10:10:15',3,'set'),
(3,'2010-10-10 10:10:20',3,'put'),
(4,'2010-10-10 00:00:00',6,'get'),
(5,'2010-10-11 00:00:00',6,'set'),
(6,'2010-10-12 00:00:00',6,'put');

SELECT o.id order_id
     , o.data
     , o.created_at order_created_at
     , a.id activity_id
     , a.verb
     , a.created_at activity_created_at
  FROM activities a
  JOIN orders o
    ON o.foreign_id = a.foreign_id
  JOIN
     ( SELECT a.foreign_id
            , MIN(ABS(TIMEDIFF(a.created_at,o.created_at))) x
         FROM activities a
         JOIN orders o
           ON o.foreign_id = a.foreign_id
        GROUP
           BY a.foreign_id
     ) m
    ON m.foreign_id = a.foreign_id
   AND m.x = ABS(TIMEDIFF(a.created_at,o.created_at))
 UNION DISTINCT
SELECT o.id
     , o.data
     , o.created_at
     , a.id
     , a.verb
     , a.created_at
  FROM orders o
  LEFT
  JOIN activities a
    ON a.foreign_id = o.foreign_id
 WHERE a.foreign_id IS NULL;
;

+----------+---------+---------------------+-------------+------+---------------------+
| order_id | data    | order_created_at    | activity_id | verb | activity_created_at |
+----------+---------+---------------------+-------------+------+---------------------+
|        1 | order 1 | 2010-10-10 10:10:10 |           1 | get  | 2010-10-10 10:10:10 |
|        4 | order 4 | 2010-10-10 00:00:00 |           4 | get  | 2010-10-10 00:00:00 |
|        5 | order 5 | 2010-10-10 00:00:00 |        NULL | NULL | NULL                |
+----------+---------+---------------------+-------------+------+---------------------+

关于mysql - MySQL-来自右表的连接行上的条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15341525/