gpt4 book ai didi

MySQL FIND_IN_SET 不工作

转载 作者:行者123 更新时间:2023-11-29 12:04:38 25 4
gpt4 key购买 nike

我有这些表

伊拉西表

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 FIND_IN_SET 不工作,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31740400/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com