主页 / 问题 / 628464
Accepted
cruim
Asked:2020-02-16 14:24:57 +0800 CST2020-02-16 14:24:57 +0800 CST

左右连接

  • 772

我做了一个三重左/右连接，以在三列中显示三个查询的结果。

SELECT t1.order_number as order_t1, t2.order_number as order_t2, t3.order_number as order_t3 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')

        ) as t1
        LEFT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t2
        ON t1.order_id  = t2.order_id
LEFT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) 
AND  DATE_SUB(CURRENT_DATE, INTERVAL 10  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t3
        ON t2.order_id  = t3.order_id
    UNION ALL
        SELECT t1.order_number as order_t1, t2.order_number as order_t2, t3.order_number as order_t3 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')

        ) as t1
        RIGHT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t2
        ON t1.order_id  = t2.order_id
RIGHT JOIN
        (SELECT order_number,`order`.order_id 
                                FROM `order`
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) 
AND  DATE_SUB(CURRENT_DATE, INTERVAL 10  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t3
       ON t2.order_id  = t3.order_id

正确显示第一列和第三列中的数据，只有第二列为空。如果不加入第三个查询，那么它会正确显示第一列和第二列的结果。为什么不能显示第二列的结果？是否可以从所有列中删除空值？更新。如果只对两个查询完成，那么它会在两列中显示数据

SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')

        ) as t1
        LEFT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t2
        ON t1.order_id  = t2.order_id
    UNION ALL
        SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')

        ) as t1
        RIGHT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('Казахстан Курьеры')
        ) as t2
        ON t1.order_id  = t2.order_id
mysql

1 个回答

  1. Best Answer

    我认为您将通过以下请求获得必要的数据：

    SELECT IF(diff<7,order_number,NULL) order_t1,
       IF(diff>=7 and diff<=10,order_number,NULL) order_t2,
       IF(diff>10,order_number,NULL) order_t3
  FROM (
    SELECT `order`.order_id,order_number,datediff(CURRENT_DATE, order_statusUpdatedAt) diff
      FROM `order` 
     inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
     where order_status = 'delivered'
       AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
       and order_delivery_data_name in ('Казахстан Курьеры')
  ) X
    • 1

相关问题