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

一、使用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;