我有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是否有任何方法可以在第一个输出中尽快获得Required output。我试过了,现在被困在这里了。谢谢。
发布于 2017-05-19 19:16:56
您应该在where子句中使用AND而不是OR。你也不需要括号。
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发布于 2017-05-19 20:25:59
正确地使用圆括号环绕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我不能对我的名誉发表评论,对不起
https://stackoverflow.com/questions/44077258
复制相似问题