更改右连接到左联接

Question

在这样的查询中，您如何更改此正确连接到左键：

SELECT Z.col
FROM X INNER JOIN Y ON X.col == Y.col AND Y.status IN ('value1', 'value2')
RIGHT JOIN Z ON Z.col == X.col

以上是一个简化的查询，这是问题的实际查询：

SELECT orders.id                                                  AS id,
       orders.name                                                AS name,
       li.item                                                    AS item,
       li.size                                                    AS size,
       coalesce(fli.quantity, li.quantity)                        AS qty,
       coalesce(l.name, 'online store')                           AS store,
       timezone('UTC-2', orders.created_at)                       AS date,
       coalesce(fli.price * fli.quantity, li.price * li.quantity) AS price,
       li.total_discount                                          AS discount,
       'order'                                                    AS type
FROM fulfillment_line_items fli
         INNER JOIN fulfillments f on fli.fulfillment_id = f.id and f.status IN ('fulfilled', 'success')
         RIGHT JOIN line_item li on fli.id = li.id
         INNER JOIN orders ON li.order_id = orders.id and orders.name = '#282814'
         LEFT JOIN locations l on f.location_id = l.id;

此更改的原因是我使用sqlalchemy（python orm）不支持正确的加入。

示例数据

订单：

SELECT *
FROM ORDERS WHERE ID = 3875388358828;

+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+
|id           |created_at                       |updated_at                       |total_discounts|total_line_items_price|total_outstanding|total_price|cancelled_at|financial_status|fulfillment_status|name    |processed_at                     |source_name|note       |customer_id|location_id|user_id    |gateway|total_shipping|tags|
+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+
|3875388358828|2021-07-05 08:20:57.000000 +00:00|2021-07-09 06:34:19.000000 +00:00|0              |265                   |0                |265        |NULL        |paid            |fulfilled         |#1083501|2021-07-05 08:20:56.000000 +00:00|pos        |314496369  |-1         |63074828460|10475667509|manual |0             |NULL|
+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+

line_item：

SELECT *
FROM line_item WHERE ORDER_ID = 3875388358828;

+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+
|id            |quantity|fulfillment_status|total_discount|price|pre_tax_price|tax  |item |size|order_id     |fulfillable_quantity|
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3875388358828|0                   |
|10034198905004|1       |NULL              |0             |100  |87.72        |12.28|57575|XXL |3875388358828|0                   |
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+

符合：

SELECT *
FROM fulfillments WHERE ORDER_ID = 3875388358828;

+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+
|id           |created_at                       |updated_at                       |location_id|order_id     |status   |
+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+
|3423769559212|2021-07-05 08:20:58.000000 +00:00|2021-07-09 06:33:28.000000 +00:00|63074828460|3875388358828|cancelled|
|3430761332908|2021-07-09 06:34:11.000000 +00:00|2021-07-09 06:34:11.000000 +00:00|63074828460|3875388358828|success  |
+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+

fullillment_line_items：

SELECT *
FROM fulfillment_line_items WHERE fulfillment_id IN (3423769559212,3430761332908);
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+
|id            |quantity|fulfillment_status|total_discount|price|pre_tax_price|tax  |item |size|fulfillment_id|fulfillable_quantity|
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3423769559212 |0                   |
|10034198905004|1       |NULL              |0             |100  |87.72        |12.28|57575|XXL |3423769559212 |0                   |
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3430761332908 |0                   |
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+

预期结果：

+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+
|id           |name    |item |size|qty|store            |date                      |price|discount|type |
+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+
|3875388358828|#1083501|48901|XXL |1  |Jumia consignment|2021-07-05 10:20:57.000000|165  |0       |order|
|3875388358828|#1083501|57575|XXL |1  |online store     |2021-07-05 10:20:57.000000|100  |0       |order|
+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+

Answer 1

一个正确的加入b等于b左将加入sql中的A，因此我们可以交换加入funderment_line_items，line_item /代码>。

SELECT orders.id                                                  AS id,
        orders.name                                                AS name,
        li.item                                                    AS item,
        li.size                                                    AS size,
        coalesce(fli.quantity, li.quantity)                        AS qty,
        coalesce(l.name, 'online store')                           AS store,
        timezone('UTC-2', orders.created_at)                       AS date,
        coalesce(fli.price * fli.quantity, li.price * li.quantity) AS price,
        li.total_discount                                          AS discount,
        'order'                                                    AS type
FROM line_item li
 LEFT JOIN fulfillment_line_items fli on fli.id = li.id
 INNER JOIN fulfillments f on fli.fulfillment_id = f.id and f.status IN ('fulfilled', 'success')
 INNER JOIN orders ON li.order_id = orders.id and orders.name = '#282814'
 LEFT JOIN locations l on f.location_id = l.id;