在MySQL SELECT查询中使用IF语句 [英] Using an IF Statement in a MySQL SELECT query
问题描述
我试图在MySQL select查询中使用 IF
语句。
I am trying to use an IF
statement in a MySQL select query.
我在 AND
语句后出现错误,其中第一个 IF
。
I am getting an error after the AND
statement where the first 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;
为什么这不起作用以及在MySQL查询中执行IF语句的正确方法是什么?
Why doesn't this work and what is the proper way to do an IF statement in a MySQL query?
推荐答案
您使用的IF / THEN / ELSE结构仅在存储过程和函数中有效。您的查询将需要重新构建,因为您不能使用IF()函数来控制WHERE子句的流程,就像这样。
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()函数主要用于查询的SELECT部分,用于根据特定条件选择不同的数据,而不是要在查询的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:
SELECT IF(JQ.COURSE_ID=0, 'Some Result If True', 'Some Result If False'), OTHER_COLUMNS
FROM ...
WHERE ...
这篇关于在MySQL SELECT查询中使用IF语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!