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

Question

我有两张表：

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  |
+------+---------------------+------------+------+

现在，我需要在foreign_id列上连接activities和orders：为每个订单只选择一个活动(如果存在)，以使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，因为这一部分是更大的报告querty的一部分。

Answer 1

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

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                |
+----------+---------+---------------------+-------------+------+---------------------+