• 135456

    文章

  • 827

    评论

  • 13

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

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

2年想跳槽阿里,大咖揭秘大厂面试的那些事儿 >>

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;

 


695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

0条评论

Loading...


发表评论

电子邮件地址不会被公开。 必填项已用*标注

自定义皮肤 主体内容背景
打开支付宝扫码付款购买视频教程
遇到问题联系客服QQ:419400980
注册梁钟霖个人博客