Mysql SELECT CASE WHEN 某事然后返回字段
我有两个字段 nnmu 和 nnmi ,
if nnmu is equal to 1, I need to return naziv_mesta from **mesto_istovara**,
else if it's =0 I need to return naziv_mesta from mesto_utovara table
并且相反,
if nnmi is equal to 1, then I need to return naziv_mesta from **mesto_utovara,**
else if it's =0 need to return naziv_mesta from mesto_istovara.
起初一切看起来都不错,但不知何故它混合了值,当 nnmi 和 nnmu 都等于 0 时它起作用,但当其中一个值为 1 时它起作用返回废话。有什么帮助吗?
select u.id_utovar,
u.datum_isporuke,
u.broj_otpremnice,
r.naziv_robe,
CASE u.nnmu
WHEN u.nnmu ='0' THEN mu.naziv_mesta
WHEN u.nnmu ='1' THEN m.naziv_mesta
ELSE 'GRESKA'
END as mesto_utovara,
CASE u.nnmi
WHEN u.nnmi = '0' THEN m.naziv_mesta
WHEN u.nnmi = '1' THEN mu.naziv_mesta
ELSE 'GRESKA'
END as mesto_istovara,
m.adresa,
m.kontakt_osoba,
m.br_telefona,
u.broj_paleta,
u.bruto,
k.username,
u.napomena,
v.registracija,
p.naziv_prevoznika,
u.cena,
u.korisnik_logistika,
u.korisnik_analitika,
u.datum_unosa,
u.vreme_unosa,
u.zakljucan,
u.id_mesto_utovara,
u.id_mesto_istovara,
u.nnmu,
u.nnmi
FROM utovar u ,mesto_utovara mu, mesto_istovara m, roba r, vozila v,prevoznik p, korisnik k
WHERE u.id_mesto_istovara=m.id_mesto_istovara
and k.id_korisnik = u.korisnik
and r.id_robe=u.id_robe
and u.id_mesto_utovara = mu.id_mesto_utovara
and v.id_vozilo = u.id_vozilo
and p.id_prevoznik = u.id_prevoznik
ORDER by u.id_utovar DESC
I have two field nnmu and nnmi ,
if nnmu is equal to 1, I need to return naziv_mesta from **mesto_istovara**,
else if it's =0 I need to return naziv_mesta from mesto_utovara table
and reverse,
if nnmi is equal to 1, then I need to return naziv_mesta from **mesto_utovara,**
else if it's =0 need to return naziv_mesta from mesto_istovara.
At first everything looks good, but somehow it mix up values, it work when nnmi and nnmu both are equal to 0, but when either value is 1 it returns nonsense. Any help?
select u.id_utovar,
u.datum_isporuke,
u.broj_otpremnice,
r.naziv_robe,
CASE u.nnmu
WHEN u.nnmu ='0' THEN mu.naziv_mesta
WHEN u.nnmu ='1' THEN m.naziv_mesta
ELSE 'GRESKA'
END as mesto_utovara,
CASE u.nnmi
WHEN u.nnmi = '0' THEN m.naziv_mesta
WHEN u.nnmi = '1' THEN mu.naziv_mesta
ELSE 'GRESKA'
END as mesto_istovara,
m.adresa,
m.kontakt_osoba,
m.br_telefona,
u.broj_paleta,
u.bruto,
k.username,
u.napomena,
v.registracija,
p.naziv_prevoznika,
u.cena,
u.korisnik_logistika,
u.korisnik_analitika,
u.datum_unosa,
u.vreme_unosa,
u.zakljucan,
u.id_mesto_utovara,
u.id_mesto_istovara,
u.nnmu,
u.nnmi
FROM utovar u ,mesto_utovara mu, mesto_istovara m, roba r, vozila v,prevoznik p, korisnik k
WHERE u.id_mesto_istovara=m.id_mesto_istovara
and k.id_korisnik = u.korisnik
and r.id_robe=u.id_robe
and u.id_mesto_utovara = mu.id_mesto_utovara
and v.id_vozilo = u.id_vozilo
and p.id_prevoznik = u.id_prevoznik
ORDER by u.id_utovar DESC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您正在混合 2 个不同的
CASE 语法不恰当。
使用这种风格(搜索)
或者这种风格(简单)
不是这种风格(简单但带有布尔搜索谓词)
在 MySQL 中,这将最终进行测试
u.nnmu
是否等于布尔表达式u.nnmu ='0'
本身的值。无论u.nnmu
是1
还是0
,case 表达式本身的结果都将为1
例如if
nmu = '0'
then (nnmu ='0'
) 计算结果为true
(1) 且 (nnmu ='1 '
) 计算结果为false
(0)。将它们代入 case 表达式即可得出if
nmu = '1'
then (nnmu ='0'
) 计算结果为false
(0) 和 (nnmu ='1'
) 计算结果为true
(1)。将它们代入 case 表达式给出You are mixing the 2 different
CASE
syntaxes inappropriately.Use this style (Searched)
Or this style (Simple)
Not This (Simple but with boolean search predicates)
In MySQL this will end up testing whether
u.nnmu
is equal to the value of the boolean expressionu.nnmu ='0'
itself. Regardless of whetheru.nnmu
is1
or0
the result of the case expression itself will be1
For example if
nmu = '0'
then (nnmu ='0'
) evaluates astrue
(1) and (nnmu ='1'
) evaluates asfalse
(0). Substituting these into the case expression givesif
nmu = '1'
then (nnmu ='0'
) evaluates asfalse
(0) and (nnmu ='1'
) evaluates astrue
(1). Substituting these into the case expression gives