Mysql SELECT CASE WHEN 某事然后返回字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4356318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql SELECT CASE WHEN something then return field
提问by supermus
I have two field nnmu and nnmi,
我有两个字段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
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?
起初一切看起来都不错，但不知何故它混淆了值，当 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
回答by Martin Smith
You are mixing the 2 differentCASEsyntaxes inappropriately.
您不恰当地混合了两种不同的CASE语法。
Use this style (Searched)
使用此样式（已搜索）
CASE
WHEN u.nnmu ='0' THEN mu.naziv_mesta
WHEN u.nnmu ='1' THEN m.naziv_mesta
ELSE 'GRESKA'
END as mesto_utovara,
Or this style (Simple)
或者这种风格（简单）
CASE u.nnmu
WHEN '0' THEN mu.naziv_mesta
WHEN '1' THEN m.naziv_mesta
ELSE 'GRESKA'
END as mesto_utovara,
Not This (Simple but with boolean search predicates)
不是这个（简单但带有布尔搜索谓词）
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,
In MySQL this will end up testing whether u.nnmuis equal to the value of the boolean expression u.nnmu ='0'itself. Regardless of whether u.nnmuis 1or 0the result of the case expression itself will be 1
在 MySQL 中，这将最终测试是否u.nnmu等于布尔表达式u.nnmu ='0'本身的值。不管u.nnmuis1还是0case 表达式本身的结果都会是1
For example if nmu = '0'then (nnmu ='0') evaluates as true(1) and (nnmu ='1') evaluates as false(0). Substituting these into the case expression gives
例如，如果nmu = '0'then ( nnmu ='0') 计算为true(1) 并且 ( nnmu ='1') 计算为false(0)。将这些代入 case 表达式给出
SELECT CASE '0'
WHEN 1 THEN '0'
WHEN 0 THEN '1'
ELSE 'GRESKA'
END as mesto_utovara
if nmu = '1'then (nnmu ='0') evaluates as false(0) and (nnmu ='1') evaluates as true(1). Substituting these into the case expression gives
if nmu = '1'then ( nnmu ='0') 计算结果为false(0) 并且 ( nnmu ='1') 计算结果为true(1)。将这些代入 case 表达式给出
SELECT CASE '1'
WHEN 0 THEN '0'
WHEN 1 THEN '1'
ELSE 'GRESKA'
END as mesto_utovara
