在MySQL SELECT查询中使用IF语句

本文介绍了在MySQL SELECT查询中使用IF语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图在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 ...