mysql where if_【MYSQL函数】MYSQL中IF函数在where中的使用

WHERE IF(条件,  true执行条件, false执行条件)

业务需求：

查询SUPPLIER_CLASS=0 and tp1.`TYPE_FLAG` = 1 或者 SUPPLIER_CLASS=1

实现有两种：

一、使用IF函数

SELECT

temp.*

FROM

(SELECT

tp1.`ID` AS supplierCode,

tp1.`SUPPLIER_NAME` AS supplierName,

CASE

WHEN tp1.`SUPPLIER_TYPE` IS NULL

OR TRIM(tp1.`SUPPLIER_TYPE`) IN (

'企业',

'电商'

)

THEN '0'

WHEN tp1.`SUPPLIER_TYPE` = '个人'

THEN '2'

WHEN tp1.`SUPPLIER_TYPE` = '出资人'

THEN '6'

ELSE '4'

END AS supplierType,

tp1.`SUPPLIER_CLASS` AS supplierClass,

GROUP_CONCAT(

DISTINCT esp.`supplier_result_id`

) AS supplierResultId,

GROUP_CONCAT(

DISTINCT esp.`supplier_result_name`

) AS supplierResultName,

GROUP_CONCAT(

DISTINCT esp.`category_type_name`

) AS supplierCategoryNames,

tp1.TYPE_FLAG AS typeFlag

FROM

t_p_0001 tp1

LEFT JOIN e esp

ON tp1.`ID` = esp.`supplier_id`

WHERE tp1.`ACTIVE_FLAG` = 1

AND tp1.`REGISTER_AUDIT_STATUS` = 2

AND (

(

esp.`supplier_result_id` <> '06'

AND esp.`supplier_result_id` <> '07'

)

OR esp.`supplier_result_id` IS NULL

)

AND

IF(tp1.`SUPPLIER_CLASS` = 0,

(tp1.`SUPPLIER_CLASS` = 0

AND tp1.`TYPE_FLAG` = 1),

tp1.`SUPPLIER_CLASS` = 1)

GROUP BY tp1.ID,

tp1.`SUPPLIER_TYPE`,

tp1.`SUPPLIER_CLASS`) AS temp

WHERE 1 = 1

#AND temp.supplierType = 0

AND temp.supplierClass = 1;

二、使用or查询

SELECT

temp.*

FROM

(SELECT

tp1.`ID` AS supplierCode,

tp1.`SUPPLIER_NAME` AS supplierName,

CASE

WHEN tp1.`SUPPLIER_TYPE` IS NULL

OR TRIM(tp1.`SUPPLIER_TYPE`) IN (

'企业',

'电商'

)

THEN '0'

WHEN tp1.`SUPPLIER_TYPE` = '个人'

THEN '2'

WHEN tp1.`SUPPLIER_TYPE` = '出资人'

THEN '6'

ELSE '4'

END AS supplierType,

tp1.`SUPPLIER_CLASS` AS supplierClass,

GROUP_CONCAT(

DISTINCT esp.`supplier_result_id`

) AS supplierResultId,

GROUP_CONCAT(

DISTINCT esp.`supplier_result_name`

) AS supplierResultName,

GROUP_CONCAT(

DISTINCT esp.`category_type_name`

) AS supplierCategoryNames,

tp1.TYPE_FLAG AS typeFlag

FROM

t0001 tp1

LEFT JOIN e esp

ON tp1.`ID` = esp.`supplier_id`

WHERE tp1.`ACTIVE_FLAG` = 1

AND tp1.`REGISTER_AUDIT_STATUS` = 2

AND (

(

esp.`supplier_result_id` <> '06'

AND esp.`supplier_result_id` <> '07'

)

OR esp.`supplier_result_id` IS NULL

)

AND (

(

tp1.`SUPPLIER_CLASS` = 0

AND tp1.`TYPE_FLAG` = 1

)

OR tp1.`SUPPLIER_CLASS` = 1

)

GROUP BY tp1.ID,

tp1.`SUPPLIER_TYPE`,

tp1.`SUPPLIER_CLASS`) AS temp

WHERE 1 = 1

AND temp.supplierClass = 1;