mysql - MySQL FIND_IN_SET不起作用

我有这些桌子

伊拉克塞桌子

invoice_nr | pard_suma | pard_vad | pirk_vad
1122         200         2,4,6      2,1,3
1111         502,22      3          4
1112         5545        3          4,1
54151        1000        2          1
74411        1345,78     6          18

Apmokejimai表：

id | invoice_nr | suma | tipas
1    1122         100    2
2    1112         5545   1
3    1122         100    2
4    1111         310    2
5    54151        200    2

该查询：

select t1.invoice_nr, max(t1.pard_suma) as pardtotal, sum(t2.suma) as sumatotal
from irasai t1
left join apmokejimai t2 on t1.invoice_nr = t2.invoice_nr
WHERE t2.tipas != '1'
    OR t2.tipas IS NULL
    AND FIND_IN_SET(1, t1.pirk_vad)
    OR FIND_IN_SET(1, t1.pard_vad)
group by invoice_nr
having pardtotal <> sumatotal or sumatotal is null

结果是这样的：

invoice_nr | pard_total | sumtotal
1111         502.22       310
54151        1000         200

应该是这样的

invoice_nr | pard_total | sumtotal
54151        1000         200

我需要获取它，因为它属于ID为1的用户

最佳答案

您需要用括号将WHERE子句中的条件分组。

select t1.invoice_nr, max(t1.pard_suma) as pardtotal, sum(t2.suma) as sumatotal
from irasai t1
left join apmokejimai t2 on t1.invoice_nr = t2.invoice_nr
WHERE (t2.tipas != '1'
       OR t2.tipas IS NULL)
    AND (FIND_IN_SET(1, t1.pirk_vad)
        OR FIND_IN_SET(1, t1.pard_vad))
group by invoice_nr
having pardtotal <> sumatotal or sumatotal is null

DEMO

没有括号，AND的优先级高于OR，因此将其解释为

WHERE t2.tipas != 1
    OR (t2.tipas IS NULL
        AND
        FIND_IN_SET(1, t1.pirk_vad))
    OR FIND_IN_SET(1, t1.pard_vad)

关于mysql - MySQL FIND_IN_SET不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31740400/