left-right join

366

18 февраля 2017, 05:56

Сделал тройной left/right join, чтобы вывести в трех колонках результаты трех запросов.

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

Выводит данные в первой и третьей колонках корректно, во второй одни null. Если не джойнить третий запрос, то выводит результаты первой и второй колонок правильно. Почему может не выводить результат второй колонки? И возможно ли удалить null-значения из всех колонок? UPD. Если сделать только для двух запросов, то выводит данные в обоих колонках

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

Answer 1

Думаю нужные данные вы получите примерно таким запросом:

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