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;