mysql 查询语句 if_在MySQL SELECT查询中使用IF语句

I am trying to use an IF statement in a MySQL select query.

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;

Why doesn't this work and what is the proper way to do an IF statement in a MySQL query?

解决方案

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.

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 ...