php - 在单个查询中使用OR，AND运算符

我正在尝试列出符合我条件的记录。由于ii同时使用AND和OR运算符，所以我没有得到确切的报告。这是我的查询

SELECT o.sales_order_id AS SID, o.reference, o.status, o.last_modified, sol.sales_order_id, sol.item, sol.quantity, sol.selling_price, sol.discount, sol.tax, SUM(sol.tax_amount) AS Tamt, SUM(sol.total) as Total, i.iid, GROUP_CONCAT(DISTINCT i.name) AS iname, l.company, t.tax_id, t.name as tname, t.rate from orders o INNER JOIN before_order_line_items sol ON o.sales_order_id = sol.sales_order_id INNER JOIN leads l ON o.company_id=l.id INNER JOIN items i ON sol.item=i.iid INNER JOIN taxes t ON sol.tax=t.tax_id WHERE o.order_quote='Order' AND o.authorise='Yes' OR o.assigned_to=6 OR o.user_id=6 GROUP BY o.sales_order_id ORDER BY o.sales_order_id DESC

我将订单和报价都存储在单个表Orders中，对于订单，我将其存储为Order在order_quote列中，对于报价，它是Quote

它不检查order_quote='Order'条件，它同时显示订单和报价。

如果我删除OR o.assigned_to=6 OR o.user_id=6，它会给出正确的结果。

我尝试像这样使用DISTINCT

SELECT DISTINCT o.order_quote=`Order`, .....

但是不起作用。

更新

SELECT o.sales_order_id AS SID, o.reference, o.status, o.last_modified, sol.sales_order_id, sol.item, sol.quantity, sol.selling_price, sol.discount, sol.tax, SUM(sol.tax_amount) AS Tamt, SUM(sol.total) as Total, i.iid, GROUP_CONCAT(DISTINCT i.name) AS iname, l.company, t.tax_id, t.name as tname, t.rate from orders o INNER JOIN before_order_line_items sol ON o.sales_order_id = sol.sales_order_id INNER JOIN leads l ON o.company_id=l.id INNER JOIN items i ON sol.item=i.iid INNER JOIN taxes t ON sol.tax=t.tax_id WHERE (o.order_quote='Order' AND o.authorise='Yes') AND (o.assigned_to=6 OR o.user_id=6) GROUP BY o.sales_order_id ORDER BY o.sales_order_id DESC

最佳答案

您需要使用括号。我不确定具体如何，但是您当前的where子句解释为：

WHERE (o.order_quote = 'Order' AND o.authorise = 'Yes') OR
      (o.assigned_to = 6) OR
      (o.user_id = 6)

我猜您想要这样的东西：

WHERE (o.order_quote = 'Order' AND o.authorise = 'Yes') AND
      (o.assigned_to = 6 OR o.user_id = 6)

但这仅仅是猜测。

也许：

WHERE (o.order_quote = 'Order' AND
      (o.authorise = 'Yes' OR o.assigned_to = 6 OR o.user_id = 6)

关于php - 在单个查询中使用OR，AND运算符，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45250342/