在 MySQL SELECT 查询中使用 IF 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8444570/
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
Using an IF Statement in a MySQL SELECT query
提问by Santosh
I am trying to use an IFstatement in a MySQL select query.
我正在尝试IF在 MySQL 选择查询中使用语句。
I am getting an error after the ANDstatement where the first IF.
我在之后得到一个错误AND的语句，其中第一IF。
SELECT J.JOB_ID,E.COMPANY_NAME,J.JOB_DESC,JT.JOBTYPE_NAME,J.COMPENSATION,ST.STATE_NAME,MC.METRO_CITY_NAME,I.INDUSTRY_NAME,
J.JOB_CONTACT_PERSON,J.DT_INSRT,J.JOB_TITLE,J.JOB_EXP_DATE,J.SKILLS
FROM JOBS J
JOIN EMPLOYER E ON J.COMPANY_ID=E.COMPANY_ID
JOIN LOOKUP_JOBTYPE JT ON J.JOB_TYPE=JT.JOBTYPE_ID
JOIN LOOKUP_STATE ST ON J.STATE_ID=ST.STATE_ID
JOIN JOBS_LOCATION JL ON J.JOB_ID=JL.JOB_ID
JOIN LOOKUP_METRO_CITY MC ON JL.METRO_CITY_ID=MC.METRO_CITY_ID
JOIN LOOKUP_INDUSTRY I ON J.INDUSTRY_ID=I.INDUSTRY_ID
JOIN JOBS_QUALIFICATION JQ ON J.JOB_ID=JQ.JOB_ID
JOIN LOOKUP_DEGREE_QUALIFICATION LDQ ON LDQ.QUALIFICATION_ID = JQ.QUALIFICATION_ID
WHERE J.ACTIVE='Y' AND J.DT_INSRT > COALESCE(pEmailSntDt,DATE_SUB(SYSDATE(),INTERVAL 4 DAY))
AND
IF(JQ.COURSE_ID=0)
THEN
IF(JQ.DEGREE_ID=0)
THEN J.SKILLS LIKE CONCAT('%', pSkills,'%')
ELSE
JQ.DEGREE_ID=pDegreeId OR J.SKILLS LIKE CONCAT('%', pSkills,'%')
END IF
ELSE
JQ.COURSE_ID=pCourseId OR IF(JQ.DEGREE_ID=0)
THEN
J.SKILLS LIKE CONCAT('%', pSkills,'%')
ELSE
JQ.DEGREE_ID=pDegreeId OR J.SKILLS LIKE CONCAT('%', pSkills,'%')
END IF
END IF
GROUP BY J.JOB_ID ORDER BY J.DT_INSRT DESC;
Why doesn't this work and what is the proper way to do an IF statement in a MySQL query?
为什么这不起作用，在 MySQL 查询中执行 IF 语句的正确方法是什么？
回答by davidethell
The IF/THEN/ELSE construct you are using is only valid in stored procedures and functions. Your query will need to be restructured because you can't use the IF() function to control the flow of the WHERE clause like this.
您使用的 IF/THEN/ELSE 构造仅在存储过程和函数中有效。您的查询将需要重新构造，因为您不能使用 IF() 函数来控制 WHERE 子句的流程，就像这样。
The IF() function that can be used in queries is primarily meant to be used in the SELECT portion of the query for selecting different data based on certain conditions, not so much to be used in the WHERE portion of the query:
可以在查询中使用的 IF() 函数主要用于查询的 SELECT 部分，用于根据某些条件选择不同的数据，而不是用于查询的 WHERE 部分：
SELECT IF(JQ.COURSE_ID=0, 'Some Result If True', 'Some Result If False'), OTHER_COLUMNS
FROM ...
WHERE ...
回答by Eric Leschinski
How to use an IF statement in the MySQL "select list":
如何在MySQL“选择列表”中使用IF语句：
select if (1>2, 2, 3); //returns 3
select if(1<2,'yes','no'); //returns yes
SELECT IF(STRCMP('test','test1'),'no','yes'); //returns no
How to use an IF statement in the MySQL where clause search condition list:
如何在MySQL where子句搜索条件列表中使用IF语句：
create table penguins (id int primary key auto_increment, name varchar(100))
insert into penguins (name) values ('rico')
insert into penguins (name) values ('kowalski')
insert into penguins (name) values ('skipper')
select * from penguins where 3 = id
-->3 skipper
select * from penguins where (if (true, 2, 3)) = id
-->2 kowalski
How to use an IF statement in the MySQL "having clause search conditions":
如何在MySQL“具有子句搜索条件”中使用IF语句：
select * from penguins
where 1=1
having (if (true, 2, 3)) = id
-->1 rico
Use an IF statement with a column used in the select list to make a decision:
使用带有选择列表中使用的列的 IF 语句来做出决定：
select (if (id = 2, -1, 1)) item
from penguins
where 1=1
--> 1
--> -1
--> 1
If statements embedded in SQL queries is a bad "code smell". Bad code has high "WTF's per minute" during code review. This is one of those things. If I see this in production with your name on it, I'm going to automatically not like you.
如果嵌入在 SQL 查询中的语句是一种糟糕的“代码味道”。在代码期间，糟糕的代码具有很高的“每分钟 WTF”。这是其中之一。如果我在制作中看到你的名字，我会自动不喜欢你。
回答by Ameen Maheen
try this code worked for me
试试这个代码对我有用
SELECT user_display_image AS user_image,
user_display_name AS user_name,
invitee_phone,
(CASE WHEN invitee_status = 1 THEN "attending"
WHEN invitee_status = 2 THEN "unsure"
WHEN invitee_status = 3 THEN "declined"
WHEN invitee_status = 0 THEN "notreviwed"
END) AS invitee_status
FROM your_table
