mysql 查询没有按预期复杂地工作

Question

我有 3 个表，将所有 3 个表连接起来以获得结果。

下面是表格，

//tbl_order

order_id    order_no_first    order_no    order_no_last    order_date
---------------------------------------------------------------------
   1              C             1000            a          2017-05-16
   2              C             1001            a          2017-05-16
   3              C             1001            b          2017-05-16
   4              A             1002            a          2017-05-16
   5              A             1002            b          2017-05-16

//tbl_assign

assign_id   order_id   order_no_first   central_status   central_assign_unit
----------------------------------------------------------------------------
    1          1             C                1                   1
    2          2             C                1                   1
    3          3             C                1                   1
    4          4             A                1                   1

//tbl_unit_status

status_id    assign_id    status_status
---------------------------------------
    1           1             Cutter
    2           1             Stitch
    3           1             Delivery
    4           2             Cutter
    5           2             Stitch
    6           3             Cutter
    7           4             Cutter

我想要如下结果，

//Required output

order_id  assign_id  order_no_first  order_no  order_no_last  status_status
---------------------------------------------------------------------------
    2         2            C           1001          a           Stitch
    3         3            C           1001          b           Cutter
    4         4            A           1002          a           Cutter
    5                      A           1002          b

从表tbl_unit_status中查看status_status字段下方的状态，如果是Despatch则不显示该结果。

我尝试过得到上面的结果。但我的代码下面没有成功。

`SELECT * 
FROM tbl_order o 
LEFT JOIN tbl_assign a 
ON a.order_id = o.order_id AND o.order_no_first = a.order_no_first 
LEFT JOIN (
    SELECT u.assign_id, max(u.status_id) AS maxid 
    FROM tbl_unit_status u GROUP BY u.assign_id) uu 
ON uu.assign_id = a.assign_id 
LEFT JOIN tbl_unit_status u2 on u2.status_id = uu.maxid 
WHERE a.central_status = 1 AND a.central_assign_unit = 1 
    OR (u2.status_status != "Delivery" AND u2.status_status != "Despatch") 
GROUP BY o.order_id

从上面的代码来看，结果是

//wrong output

order_id  assign_id  order_no_first  order_no  order_no_last  status_status
---------------------------------------------------------------------------
    1         1            C           1000          a           Delivery
    2         2            C           1001          a           Stitch
    3         3            C           1001          b           Cutter
    4         4            A           1002          a           Cutter
    5                      A           1002          b

有什么方法可以在第一个输出中尽快获得所需的输出。我已经尝试过，但被困在这里。谢谢。

Answer 1

在 AND OR 子句中正确使用括号，我建议在处理字符串时使用 NOT LIKE 而不是 !=。

SELECT * 
FROM tbl_order o 
LEFT JOIN tbl_assign a 
ON a.order_id = o.order_id AND o.order_no_first = a.order_no_first 
LEFT JOIN (
    SELECT u.assign_id, max(u.status_id) AS maxid 
    FROM tbl_unit_status u GROUP BY u.assign_id) uu 
ON uu.assign_id = a.assign_id 
LEFT JOIN tbl_unit_status u2 on u2.status_id = uu.maxid 
WHERE a.central_status = 1 AND (a.central_assign_unit = 1 OR u2.status_status NOT LIKE 'Delivery' AND u2.status_status NOT LIKE 'Despatch') 
GROUP BY o.order_id

Obs:由于我的声誉，我无法发表评论，抱歉