首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >mysql查询不按预期复杂工作

问mysql查询不按预期复杂工作
EN

Stack Overflow用户
提问于 2017-05-19 19:00:57
回答 2查看 36关注 0票数 0

我有3个表,其中所有3个表被连接起来,以获得结果。

下面是桌子,

代码语言:javascript
复制
//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

我要的结果如下,

代码语言:javascript
复制
//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,则不要显示结果。

我已经试着得到上面的结果。但下面没有成功是我的准则。

代码语言:javascript
复制
`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

根据上面的代码,结果是

代码语言:javascript
复制
//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

是否有任何方法可以在第一个输出中尽快获得Required output。我试过了,现在被困在这里了。谢谢。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-05-19 19:16:56

您应该在where子句中使用AND而不是OR。你也不需要括号。

代码语言:javascript
复制
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 
    AND (
             u2.status_status IS NULL  
         OR
             u2.status_status NOT IN ("Delivery", "Despatch")
        )

GROUP BY o.order_id
票数 0
EN

Stack Overflow用户

发布于 2017-05-19 20:25:59

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

代码语言:javascript
复制
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

我不能对我的名誉发表评论,对不起

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/44077258

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档