SQL CASE的使用例子

最新推荐文章于 2025-05-17 21:53:35 发布

原创最新推荐文章于 2025-05-17 21:53:35 发布 · 656 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

SQL 专栏收录该内容

2 篇文章

订阅专栏

在SQL的CASE语句相当于常用的编程逻辑if ，else，只要前面的条件满足，就不会再去执行下面的语句

例子：

select principal_ids,principal_names from materials_data
where material_category like '%Z003%' and department_name = '新产品开发中心'
and part_no like
case
   when (select count(1) from materials_data where material_category like '%Z003%' and department_name = '新产品开发中心'
   and part_no like '%E06%' ) >= 1 THEN '%E06%'
   WHEN (select count(1) from materials_data where material_category like '%Z003%' and department_name = '新产品开发中心'
   and part_no like '%E06%' ) < 1 THEN ''
END

也可以写成如下：

select principal_ids,principal_names from materials_data
where material_category like '%Z003%' and department_name = '新产品开发中心'
and part_no like
case
   when (select count(1) from materials_data where material_category like '%Z003%' and department_name = '新产品开发中心'
   and part_no like'%E06%' ) > 1 THEN '%E06%'
   WHEN (select count(1) from materials_data where material_category like '%Z003%' and department_name = '新产品开发中心'
   and part_no like '%E06%' ) < 1 THEN ''
ELSE '%E06%' END