模拟UNION式查询的MySQL复杂左连接

Question

我有4个MySQL表，sh_comenzi，sh_comenzi_prods，sh_delivery，sh_delivery_items。

表数据如下所示：

sh_comenzi表：

id  pid doc_date    status  deleted date_added  date_modified
1   12  1427534088  0       0       1427534088  1427534088
2   14  1427534088  1       0       1427534088  1427534088
3   17  1427534088  0       0       1427534088  1427534088
4   1   1427534088  0       0       1427534088  1427534088
5   2   1427534088  1       0       1427534088  1427534088

sh_comenzi_prods表：

id  idc pOrder  cPos    fid idp quantity    delivery_date
1   2   aaa     1       1   2   100         1427534088
2   2   aaa     2       1   89  100         1427534088
3   5   qqq     1       2   10  100         1427534088
4   5   qqq     2       2   11  100         1427534088
5   5   qqq     3       2   10  100         1427534088
6   5   qqq     4       2   14  100         1427534088
7   4   1234q   1       1   135 100         1427534088
8   5   123a    1       2   2   100         1427534088

sh_delivery表结构

id  status  date_added
1   0       1427534088
2   1       1427534088
3   0       1427534088
4   1       1427534088

sh_delivery_items表：

id  delivery_id idc cPos
1   1           1   1
2   2           1   1
3   4           5   1
4   4           5   2
5   4           5   4

我希望有一个MySQL查询，它向我显示一个选定的sh_comenzi id (假设是sh_comenzi.id = 5)的所有来自sh_comenzi_prods的记录，这些记录在sh_delivery_items中不存在，条件是sh_comenzi_prods.idc = sh_delivery_items.idc、sh_comenzi.status = 1和sh_delivery.status = 0。现在，对于困难的部分，我希望能够使用delivery_id = 4和sh_delivery_items.idc = 5添加来自sh_delivery_items的所有记录

这些表通过以下条件彼此关联：- sh_comenzi.id = sh_comenzi_prods.idc - sh_delivery.id = sh_delivery_items.delivery_id - sh_comenzi_prods.idc = sh_delivery_items.idc

谁能给我一个没有UNION的查询的想法，给出下一个结果？

delivery_id idc cPos
4           5   1
4           5   2
4           5   4
NULL        5   3
NULL        2   2

我使用的查询：

SELECT sh_delivery_items.delivery_id, sh_comenzi_prods.idc, sh_comenzi_prods.cPos
        FROM   sh_comenzi_prods
        LEFT JOIN sh_comenzi ON sh_comenzi.id = sh_comenzi_prods.idc
        LEFT JOIN sh_delivery_items ON (sh_delivery_items.idc = sh_comenzi_prods.idc AND sh_delivery_items.cPos = sh_comenzi_prods.cPos)
        LEFT JOIN sh_delivery ON sh_delivery.id = sh_delivery_items.delivery_id
        WHERE sh_comenzi.status=1 AND idc=5 AND (delivery_id=4 OR (sh_delivery_items.id IS NULL OR sh_delivery.status=0))
        ORDER BY sh_comenzi_prods.cPosasc

下面是sqlfiddle：http://sqlfiddle.com/#!9/c775e/2

Answer 1

您的需求有点难以满足--这就是为什么样本数据和期望的结果总是对问题有帮助。

但是，您的查询可能有一个逻辑流程，即结合使用where和left join。where中的条件通常只出现在一系列left join条件中的第一个表中。因此，以下代码可能会执行您想要的操作：

    SELECT sh_delivery_items.delivery_id, sh_comenzi_prods.idc, sh_comenzi_prods.cPos
    FROM sh_comenzi_prods LEFT JOIN
         sh_comenzi
         ON sh_comenzi.id = sh_comenzi_prods.idc AND
            sh_comenzi.status = 1 LEFT JOIN
         sh_delivery_items
         ON sh_delivery_items.idc = sh_comenzi_prods.idc AND 
            sh_delivery_items.cPos = sh_comenzi_prods.cPos LEFT JOIN
         sh_delivery
         ON sh_delivery.id = sh_delivery_items.delivery_id AND
            (delivery_id = 4 OR sh_delivery.status = 0)
    WHERE sh_comenzi_prods.idc = 5
    ORDER BY sh_comenzi_prods.cPosasc