WHERE IF(条件, true执行条件, false执行条件)
业务需求:
查询SUPPLIER_CLASS=0 and tp1.`TYPE_FLAG` = 1 或者 SUPPLIER_CLASS=1
实现有两种:函数
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; 2、使用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;