首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >许多到多个左联接,其中行是根据日期的最小差异连接的。

问许多到多个左联接,其中行是根据日期的最小差异连接的。
EN

Stack Overflow用户
提问于 2018-11-20 05:13:20
回答 1查看 191关注 0票数 2

我试图连接两个表,一个包含交易数据,另一个来自旅行订单。我的目标是将事务表中的每个记录与旅行订单表中的单个记录联系起来,其中事务购买日期和旅行订单开始日期之间的差异最小。这些表由SSN连接,我希望使用左联接,因为我希望在事务数据中保存所有记录,即使SSN不存在旅行订单。

以下是一些合成的样本数据:

代码语言:javascript
复制
 /* Create and populate transactions */
    CREATE TABLE transactions (
        ssn INT,
        purchase_date DATE,
        price FLOAT
    );

    INSERT INTO transactions VALUES 
        (1111, "2018-12-31", 12.20),
        (1111, "2018-11-01", 22.23),
        (2222, "2018-08-17", 99.23),
        (4444, "2018-06-07", 13.22),
        (5555, "2018-03-05", 22.22),
        (6666, "2018-05-29", 11.11),
        (7777, "2018-10-10", 23.32),
        (8888, "2018-06-21", 44.44),
        (8888, "2018-01-19", 55.55),
        (8888, "2018-02-25", 66.53);

    /* Create and populate travel orders */
    CREATE TABLE travel_orders (
        ssn_id INT NOT NULL,
        start_date DATE
        );

    INSERT INTO travel_orders VALUES 
        (1111, "2018-12-28"),
        (1111, "2018-12-07"),
        (2222, "2018-08-12"),
        (7777, "2018-10-10"),
        (7777, "2018-10-14"),
        (8888, "2018-06-18"),
        (8888, "2018-01-19"),
        (8888, "2018-02-22");

因此,例如,事务表记录

代码语言:javascript
复制
(8888, "2018-06-21", 44.44)

会加入旅行订单记录

代码语言:javascript
复制
(8888, "2018-06-18")

其他的记录也是如此。

编辑:预期的输出如下所示:

代码语言:javascript
复制
+------+---------------+-------+--------+------------+
| ssn  | purchase_date | price | ssn_id | start_date |
+------+---------------+-------+--------+------------+
| 1111 | 2018-12-31    |  12.2 |   1111 | 2018-12-28 |
| 1111 | 2018-11-01    | 22.23 |   1111 | 2018-12-07 |
| 2222 | 2018-08-17    | 99.23 |   2222 | 2018-08-12 |
| 4444 | 2018-06-07    | 13.22 |   4444 | NULL       |
| 5555 | 2018-03-05    | 22.22 |   5555 | NULL       |
| 6666 | 2018-05-29    | 11.11 |   6666 | NULL       |
| 7777 | 2018-10-10    | 23.32 |   7777 | 2018-10-10 |
| 8888 | 2018-06-21    | 44.44 |   8888 | 2018-06-18 |
| 8888 | 2018-01-19    | 55.55 |   8888 | 2018-01-19 |
| 8888 | 2018-02-25    | 66.53 |   8888 | 2018-02-22 |
+------+---------------+-------+--------+------------+

我有一些基本的初学者代码

代码语言:javascript
复制
SELECT t.*,
       o.*
FROM transactions AS t
LEFT JOIN travel_orders AS o
ON t.ssn = o.ssn_id;

但是我需要添加过滤器,根据购买日期和开始日期之间的最小日期差异来匹配记录。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-11-20 05:40:48

此查询将给出所需的结果。它为ssn和购买日期(子查询)的每个组合找出购买日期和开始日期之间最短的时间,然后查找到transactions和travel_orders表的JOINs,以获得所需的输出:

代码语言:javascript
复制
SELECT t.*,
       o.*
FROM transactions t
JOIN (SELECT t.ssn, 
             t.purchase_date,
             MIN(ABS(DATEDIFF(o.start_date, t.purchase_date))) AS dd
             FROM transactions t
             LEFT JOIN travel_orders o
             ON t.ssn = o.ssn_id
             GROUP BY t.ssn, t.purchase_date) d
    ON d.ssn = t.ssn AND d.purchase_date = t.purchase_date
LEFT JOIN travel_orders o
    ON o.ssn_id = t.ssn AND ABS(DATEDIFF(o.start_date, t.purchase_date)) = d.dd
ORDER BY t.ssn, t.price

输出:

代码语言:javascript
复制
ssn     purchase_date   price   ssn_id  start_date
1111    2018-12-31      12.2    1111    2018-12-28
1111    2018-11-01      22.23   1111    2018-12-07
2222    2018-08-17      99.23   2222    2018-08-12
4444    2018-06-07      13.22   (null)  (null)
5555    2018-03-05      22.22   (null)  (null)
6666    2018-05-29      11.11   (null)  (null)
7777    2018-10-10      23.32   7777    2018-10-10
8888    2018-06-21      44.44   8888    2018-06-18
8888    2018-01-19      55.55   8888    2018-01-19
8888    2018-02-25      66.53   8888    2018-02-22

基于SQLFiddle的演示

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/53386614

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档