case when 写在where条件中

最新推荐文章于 2024-08-06 12:05:05 发布
最新推荐文章于 2024-08-06 12:05:05 发布
阅读量2.9w 收藏 12
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yueyeqingshan/article/details/52299795
版权 
<pre name="code" class="sql">select datas.id,
       datas.name,
       datas.memo,
       to_char(datas.create_date, 'yyyy-mm-dd hh24:mi:ss'),
       to_char(datas.create_date, 'yyyy-mm-dd hh24:mi:ss')
  from t_stl_s_backlog datas,
       (select t.id as id,
               t.stage as stage,
               (case
                 when t.type in ('37', '38') then --  客户信息
                  'CUSTOMER'
                 when t.type in ('39', '40') then -- 项目信息
                  'PROJECT'
                 when t.type in ('41', '42') then -- 租赁物信
                  'LEASEHOLD'
                 when t.type in ('43', '44') then -- 商业机会信息
                  'OPPORTUNITY'
                 when t.type in ('45', '46') then -- 交易信息
                  'TRADEINFO'
                 when t.type in ('47', '48') then -- 交易机会（债券）
                  'TRADINGOPPORTUNITY'
                 when t.type in ('53', '54') then -- 交易机会（贷款）
                  'TRADINGOPPORTUNITYLOAN'
                 when t.type in ('49', '50') then -- 租期检查信息  
                  'LEASECHECK'
                 when t.type in ('51', '52') then -- 质量分类信息
                  'QUALITYCLASSIFY'
                 else
                  'other'
               end) as typeMark
          from t_stl_s_backlog t
         inner join t_stl_s_organization organizati1_
            on t.fk_organization_id = organizati1_.id
         where t.type in ('37',
                          '38',
                          '39',
                          '40',
                          '41',
                          '42',
                          '47',
                          '48',
                          '43',
                          '44',
                          '45',
                          '46',
                          '49',
                          '50',
                          '51',
                          '52',
                          '53',
                          '54')
           and organizati1_.id in
               ('40280c1250ac38680150ac5845f20004',
                '40280c1250ac38680150ac5a74ff0007',
                '40280c1250ac38680150ac5afed20008',
                '40280c66508e2f7e01508e4133ce0000',
                '40280c1250ac38680150ac58235c0003',
                '1',
                '40280c96533a921c01533a9b2be30000',
                '40280c96533a921c01533a9c40fb0001',
                '4028d08154c86e630154c8c5804e0001')
           and to_char(t.end_date, 'yyyy-mm-dd hh24:mi:ss') >=
               '2016-08-24 00:00:00') data1,
       (select nvl(max(a.analyze_customer), 0) as analyze_customer, --客户分析阶段
               nvl(max(a.censor_customer), 0) as censor_customer, --客户审查阶段
               nvl(max(a.compliance_review_project), 0) as compliance_review_project, -- 项目合规审查阶段
               nvl(max(a.dispose_leasehold), 0) as dispose_leasehold, -- 租赁物处置阶段
               nvl(max(a.due_diligence_project), 0) as due_diligence_project, --项目尽职调查阶段
               nvl(max(a.examine_project), 0) as examine_project, -- 项目项目审批阶段
               nvl(max(a.factor_contract), 0),
               nvl(max(a.finish_contract), 0),
               nvl(max(a.initiation_opportunity), 0) as initiation_opportunity, -- 商业机会项目立项阶段
               nvl(max(a.insure_leasehold), 0) as insure_leasehold, -- 租赁物保险阶段
               nvl(max(a.intervene_opportunity), 0) as intervene_opportunity, -- 商业机会前期介入阶段
               nvl(max(a.investigate_customer), 0) as investigate_customer, -- 客户调查阶段
               nvl(max(a.manage_leasehold), 0) as manage_leasehold, -- 租赁物租期管理阶段
               nvl(max(a.prepare_contract), 0),
               nvl(max(a.pricing_leasehold), 0) as pricing_leasehold, -- 租赁物认定及定价阶段
               nvl(max(a.register_leasehold), 0) as register_leasehold, -- 租赁物交接及登记阶段
               nvl(max(a.sign_contract), 0),
               nvl(max(a.sign_project), 0) as sign_project, -- 项目 签约付款阶段
               nvl(max(a.start_contract), 0),
               nvl(max(a.contact_customer), 0) as contact_customer, -- 客户联络阶段
               nvl(max(a.project_start), 0),
               nvl(max(a.review_project), 0) as review_project, -- 项目评审审查阶段
               nvl(max(a.cancel_guarantee_contract), 0),
               nvl(max(a.prepare_guarantee_contract), 0),
               nvl(max(a.sign_guarantee_contract), 0),
               nvl(max(a.commu_trading_opportunity), 0) as commu_trading_opportunity, -- 交易机会沟通阶段
               nvl(max(a.init_trading_opportunity), 0) as init_trading_opportunity, -- 交易机会立项阶段
               nvl(max(a.finish_financing_contract), 0),
               nvl(max(a.prepare_financing_contract), 0),
               nvl(max(a.sign_financing_contract), 0),
               nvl(max(a.credit_apply), 0) as credit_apply, -- 交易授信申请阶段
               nvl(max(a.credit_approve), 0) as credit_approve, -- 交易授信获批阶段
               nvl(max(a.credit_end), 0) as credit_end, -- 交易授信到期阶段
               nvl(max(a.credit_start), 0) as credit_start, -- 交易授信使用阶段
               nvl(max(a.analysis_lease_check), 0) as analysis_lease_check, --  租期检测的分析阶段
               nvl(max(a.analysis_quality_classify), 0) as analysis_quality_classify, -- 质量分类初分阶段
               nvl(max(a.approve_quality_classify), 0) as approve_quality_classify, -- 质量分类中分阶段
               nvl(max(a.execute_lease_check), 0) as execute_lease_check, -- 租期检查执行阶段
               nvl(max(a.plan_lease_check), 0) as plan_lease_check, -- 租期检测的计划阶段
               nvl(max(a.plan_quality_classify), 0) as plan_quality_classify -- 质量分类计划阶段
          from (select qx.*, zjb.user_id as fk_user_id
                  from T_STL_S_POSITION_PRIVILEGE qx,
                       T_STL_S_POSITION           gw,
                       T_STL_S_USER_POSITION      zjb
                 where zjb.user_id = '4028ef8156a07f530156a08b3cb3010b'
                   and zjb.position_id = gw.id
                   and gw.fk_privilege_id = qx.id) a
         group by a.fk_user_id) data2

 where datas.id = data1.id
      -- 建立阶段与岗位权限的映射关系 
   and (case
       -- 客户阶段权限
         when data1.typeMark = 'CUSTOMER' and data1.stage = '1' then -- 开始阶段对应的是客户联络阶段
          data2.contact_customer
         when data1.typeMark = 'CUSTOMER' and data1.stage = '2' then -- 联络阶段对应的是客户联络阶段
          data2.contact_customer
         when data1.typeMark = 'CUSTOMER' and data1.stage = '3' then -- 调查阶段对应的是客户调查阶段
          data2.investigate_customer
         when data1.typeMark = 'CUSTOMER' and data1.stage = '4' then -- 审查阶段对应的是客户审查阶段
          data2.censor_customer
         when data1.typeMark = 'CUSTOMER' and data1.stage = '5' then -- 分析阶段对应的是客户分析阶段
          data2.analyze_customer
         when data1.typeMark = 'CUSTOMER' and data1.stage = '6' then -- 结束阶段对应的是客户分析阶段
          data2.analyze_customer
       -- 项目
         when data1.typeMark = 'PROJECT' and data1.stage = '1' then -- 开始阶段对应的是项目开始阶段
          data2.due_diligence_project
         when data1.typeMark = 'PROJECT' and data1.stage = '2' then -- 尽职调查阶段对应的是项目尽职调查阶段
          data2.due_diligence_project
         when data1.typeMark = 'PROJECT' and data1.stage = '3' then -- 合规审查阶段对应的是项目合规审查阶段
          data2.compliance_review_project
         when data1.typeMark = 'PROJECT' and data1.stage = '4' then -- 评审审查阶段对应的是项目评审审查阶段
          data2.review_project
         when data1.typeMark = 'PROJECT' and data1.stage = '5' then -- 项目审批阶段对应的是项目项目审批阶段
          data2.examine_project
         when data1.typeMark = 'PROJECT' and data1.stage = '6' then -- 签约付款阶段对应的是项目签约付款阶段
          data2.sign_project
         when data1.typeMark = 'PROJECT' and data1.stage = '7' then -- 结束阶段对应的是项目签约付款阶段
          data2.sign_project
       -- 租赁物
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '1' then -- 开始阶段对应的是租赁物认定及定价阶段
          data2.pricing_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '2' then -- 认定及定价阶段对应的是租赁物认定及定价阶段
          data2.pricing_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '3' then -- 交接及登记价阶段对应的是租赁物交接及登记阶段
          data2.register_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '4' then -- 保险价阶段对应的是租赁物保险价阶段
          data2.insure_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '5' then -- 租期管理阶段对应的是租赁物租期管理阶段
          data2.manage_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '6' then -- 处置阶段对应的是租赁物处置阶段
          data2.dispose_leasehold
         when data1.typeMark = 'LEASEHOLD' and data1.stage = '7' then -- 结束阶段对应的是租赁物处置阶段
          data2.dispose_leasehold
       -- 商业机会  
         when data1.typeMark = 'OPPORTUNITY' and data1.stage = '1' then -- 开始阶段对应的是商业机会前期介入阶段
          data2.intervene_opportunity
         when data1.typeMark = 'OPPORTUNITY' and data1.stage = '2' then -- 前期介入阶段对应的是商业机会前期介入阶段
          data2.intervene_opportunity
         when data1.typeMark = 'OPPORTUNITY' and data1.stage = '3' then -- 项目立项阶段对应的是商业机会项目立项阶段
          data2.initiation_opportunity
         when data1.typeMark = 'OPPORTUNITY' and data1.stage = '4' then -- 结束阶段对应的是商业机会项目立项阶段
          data2.initiation_opportunity
       -- 交易信息    
         when data1.typeMark = 'TRADEINFO' and data1.stage = '1' then -- 开始阶段对应的是交易授信申请阶段
          data2.credit_apply
         when data1.typeMark = 'TRADEINFO' and data1.stage = '2' then -- 授信申请阶段对应的是交易授信申请阶段
          data2.credit_apply
         when data1.typeMark = 'TRADEINFO' and data1.stage = '3' then -- 授信获批阶段对应的是交易授信获批阶段
          data2.credit_approve
         when data1.typeMark = 'TRADEINFO' and data1.stage = '4' then -- 授信使用阶段对应的是交易授信使用阶段
          data2.credit_start
         when data1.typeMark = 'TRADEINFO' and data1.stage = '5' then -- 授信到期阶段对应的是交易授信到期阶段
          data2.credit_end
         when data1.typeMark = 'TRADEINFO' and data1.stage = '6' then -- 结束阶段对应的是交易授信到期阶段
          data2.credit_end
       -- 交易机会 （债券）
         when data1.typeMark = 'TRADINGOPPORTUNITY' and data1.stage = '1' then -- 开始阶段对应的是交易机会沟通阶段
          data2.commu_trading_opportunity
         when data1.typeMark = 'TRADINGOPPORTUNITY' and data1.stage = '2' then -- 沟通阶段对应的是交易机会沟通阶段
          data2.commu_trading_opportunity
         when data1.typeMark = 'TRADINGOPPORTUNITY' and data1.stage = '3' then -- 立项阶段对应的是交易机会立项阶段
          data2.init_trading_opportunity
         when data1.typeMark = 'TRADINGOPPORTUNITY' and data1.stage = '4' then -- 结束阶段对应的是交易机会立项阶段
          data2.init_trading_opportunity
       -- 交易机会 （贷款）  
         when data1.typeMark = 'TRADINGOPPORTUNITYLOAN' and
              data1.stage in ('1', '2', '3') then -- 开始、沟通、结束 阶段对应的是交易机会沟通阶段
          data2.commu_trading_opportunity
       -- 租期检查
         when data1.typeMark = 'LEASECHECK' and
              datas.entity_id =
              (select tsl.id
                 from T_STL_S_LEASE_CHECK tsl, T_STL_S_LEASE_CHECK_USER tslu
                where tsl.id = datas.entity_id
                  and tslu.lease_check_id = tsl.id
                  and tslu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '1' then -- 开始阶段对应的是租期检测的计划阶段
          data2.plan_lease_check
         when data1.typeMark = 'LEASECHECK' and
              datas.entity_id =
              (select tsl.id
                 from T_STL_S_LEASE_CHECK tsl, T_STL_S_LEASE_CHECK_USER tslu
                where tsl.id = datas.entity_id
                  and tslu.lease_check_id = tsl.id
                  and tslu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '2' then -- 计划阶段对应的是租期检测的计划阶段
          data2.plan_lease_check
         when data1.typeMark = 'LEASECHECK' and
              datas.entity_id =
              (select tsl.id
                 from T_STL_S_LEASE_CHECK tsl, T_STL_S_LEASE_CHECK_USER tslu
                where tsl.id = datas.entity_id
                  and tslu.lease_check_id = tsl.id
                  and tslu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '3' then -- 执行阶段对应的是租期检测的执行阶段
          data2.execute_lease_check
         when data1.typeMark = 'LEASECHECK' and
              datas.entity_id =
              (select tsl.id
                 from T_STL_S_LEASE_CHECK tsl, T_STL_S_LEASE_CHECK_USER tslu
                where tsl.id = datas.entity_id
                  and tslu.lease_check_id = tsl.id
                  and tslu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '4' then -- 分析阶段对应的是租期检测的分析阶段
          data2.analysis_lease_check
         when data1.typeMark = 'LEASECHECK' and
              datas.entity_id =
              (select tsl.id
                 from T_STL_S_LEASE_CHECK tsl, T_STL_S_LEASE_CHECK_USER tslu
                where tsl.id = datas.entity_id
                  and tslu.lease_check_id = tsl.id
                  and tslu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '5' then -- 分析阶段对应的是租期检测的分析阶段
          data2.analysis_lease_check
       --质量分类
         when data1.typeMark = 'QUALITYCLASSIFY' and
              datas.entity_id =
              (select tsq.id
                 from T_STL_S_QUALITY_CLASSIFY      tsq,
                      T_STL_S_QUALITY_CLASSIFY_USER tsqu
                where tsq.id = datas.entity_id
                  and tsqu.quality_classify_id = tsq.id
                  and tsqu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '1' then -- 开始阶段对应的是质量分类的计划阶段
          data2.plan_quality_classify
         when data1.typeMark = 'QUALITYCLASSIFY' and
              datas.entity_id =
              (select tsq.id
                 from T_STL_S_QUALITY_CLASSIFY      tsq,
                      T_STL_S_QUALITY_CLASSIFY_USER tsqu
                where tsq.id = datas.entity_id
                  and tsqu.quality_classify_id = tsq.id
                  and tsqu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '2' then -- 计划阶段对应的是质量分类的计划阶段
          data2.plan_quality_classify
         when data1.typeMark = 'QUALITYCLASSIFY' and
              datas.entity_id =
              (select tsq.id
                 from T_STL_S_QUALITY_CLASSIFY      tsq,
                      T_STL_S_QUALITY_CLASSIFY_USER tsqu
                where tsq.id = datas.entity_id
                  and tsqu.quality_classify_id = tsq.id
                  and tsqu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '3' then -- 初分阶段对应的是质量分类的初分阶段
          data2.analysis_quality_classify
         when data1.typeMark = 'QUALITYCLASSIFY' and
              datas.entity_id =
              (select tsq.id
                 from T_STL_S_QUALITY_CLASSIFY      tsq,
                      T_STL_S_QUALITY_CLASSIFY_USER tsqu
                where tsq.id = datas.entity_id
                  and tsqu.quality_classify_id = tsq.id
                  and tsqu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '4' then -- 终分阶段对应的是质量分类的终分阶段
          data2.approve_quality_classify
         when data1.typeMark = 'QUALITYCLASSIFY' and
              datas.entity_id =
              (select tsq.id
                 from T_STL_S_QUALITY_CLASSIFY      tsq,
                      T_STL_S_QUALITY_CLASSIFY_USER tsqu
                where tsq.id = datas.entity_id
                  and tsqu.quality_classify_id = tsq.id
                  and tsqu.user_id = '4028ef8156a07f530156a08b3cb3010b') and
              data1.stage = '5' then -- 结束阶段对应的是质量分类的终分阶段
          data2.approve_quality_classify
       -- 如果上面的条件不满足 则不应该展示数据  
         else
          2
       end) = 1
 order by datas.create_date desc;




                

        

        

    

  


    

      

        

            

              
                
                  yueyeqingshan
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    12
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
Case When在Where可变查询条件中的应用

				
			

			

				

					Mobidogs Blog 
				

				

					07-03
					
					3195
					
				

			

		

		

			
				
/*根据flag的标识值, 用不同的where条件进行查询订单列表*/CREATE PROCEDURE  [dbo].[usp_GetOrderList]@orderNo nvarchar(20),@flag intasSELECT a.OrderId,a.OrderNo,a.OrderDate,a.CustomerId,b.CustomerNameFROM Orders 

			
		

	



                

              
                



	

		

			

				
					
SQL语句之CASE WHEN

					
热门推荐

				
			

			

				

					yyyiue的博客
				

				

					11-10
					
					2万+
					
				

			

		

		

			
				
CASE WHEN 基本概念Case具有两种格式：简单Case函数和Case搜索函数。简单Case函数： 
CASE sex 
WHEN ‘1’ THEN ‘男’ 
WHEN ‘0’ THEN ‘女’ 
ELSE ‘其他’ ENDCase搜索函数： 
CASE WHEN sex = ‘1’ THEN ‘男’ 
WHEN sex = ‘0’ THEN ‘女’ 
ELSE ‘其他’ END显然，简单Ca

			
		

	



                


  
              

                


	

		

			

				
					
关于 MyBatis 我总结了 10 种通用的写法

				
			

			

				

					Java笔记虾
				

				

					10-03
					
					287
					
				

			

		

		

			
				
点击上方“后端技术精选”，选择“置顶公众号”技术文章第一时间送达！作者：smile_lgblog.csdn.net/smile_lg/article/details/71...

			
		

	





	

		

			

				
					
oracle where case when where后面跟case when

				
			

			

				

					04-21
				

			

		

		

			
				
NULL
博文链接：https://vernonchen163.iteye.com/blog/1908053

			
		

	



		

			
		



	

		

			

				
					
MYSQL中where 中 使用CASE WHEN

					
最新发布

				
			

			

				

					RoadLong的博客
				

				

					08-06
					
					646
					
				

			

		

		

			
				
MYSQL中where嵌套使用CASE WHEN

			
		

	





	

		

			

				
					
where条件中使用case when是什么鬼？

				
			

			

				

					xiaokanfuchen86的博客
				

				

					08-27
					
					2974
					
				

			

		

		

			
				
case when

			
		

	





	

		

			

				
					
where 条件 in 判断语句中使用case when

				
			

			

				

					u013276512的博客
				

				

					09-25
					
					5907
					
				

			

		

		

			
				
实战干货
case when 一般用在 select 后面或者where条件的=后面，如：
select case when '${unit}'='单' then order_num else quantity_num 
from a
where project = case when '${type}'='T1' then 'P1' else 'P2' 

如果type是一个特殊值时，需要使用where条件in逻辑筛选数据，那就会有下面的写法：
where project in (case when '$

			
		

	





	

		

			

				
					
sql示例：case when作为where 条件

				
			

			

				

					还在活水泥的未来包工头
				

				

					06-02
					
					1万+
					
				

			

		

		

			
				
上述代码中，使用了多个CASE WHEN语句在WHERE条件中进行组合，其中condition_1、condition_2和condition_3表示需要满足的条件。上述代码中，CASE WHEN语句在WHERE条件中作为一个条件，其中condition_1和condition_2表示需要满足的条件。需要注意的是，使用CASE WHEN语句进行条件过滤时，应根据具体的需求来选择使用在WHERE条件中还是SELECT语句中，以及如何与其他条件进行组合。CASE WHEN语句在WHERE条件中作为条件之一。

			
		

	





	

		

			

				
					
oracle的sql使用CASE WHEN ，可以在where条件中使用噢

				
			

			

				

					paocai_2019的博客
				

				

					11-20
					
					1万+
					
				

			

		

		

			
				
1.CASE WHEN 表达式有两种形式


--简单Case函数  

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END  

--Case搜索函数  

CASE
WHEN sex = '1' THEN '男'  
WHEN sex = '2' THEN '女'  
ELSE '其他' END

2.CASE WHEN 在语句中不同位置的用法

SELECT中使用 CASE WHEN 用法
SELECT  grade, C.

			
		

	





	

		

			

				
					
MySQL中使用case when 语句实现多条件查询的方法

				
			

			

				

					09-11
				

			

		

		

			
				
在MySQL数据库中，`CASE WHEN` 语句是一种强大的条件表达式，用于在查询中进行复杂的逻辑判断。本文将深入探讨如何使用`CASE WHEN`语句实现多条件查询，特别是针对给定的示例场景。  首先，让我们了解`CASE WHEN`的...

			
		

	





	

		

			

				
					
oracle case when 语句的用法详解

				
			

			

				

					12-16
				

			

		

		

			
				
1. CASE WHEN 表达式有两种形式 代码如下:–简单Case函数 CASE sex ... CASE WHEN 在语句中不同位置的用法2.1 SELECT CASE WHEN 用法 代码如下:SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生，2位

			
		

	





	

		

			

				
					
sql 中 case when 语法使用方法

				
			

			

				

					09-11
				

			

		

		

			
				
在SQL查询中，`CASE WHEN`常常用于根据不同的条件返回不同的值，或者在`SELECT`, `WHERE`, `GROUP BY`, `ORDER BY`等子句中进行复杂的逻辑操作。  首先，让我们了解`CASE WHEN`的基本语法。在一个普通的`SELECT`语句...

			
		

	





	

		

			

				
					
在where子句中使用case when函数

				
			

			

				

					小咸鱼gogogo~~
				

				

					12-18
					
					1万+
					
				

			

		

		

			
				
CASE WHEN 其实相当于产生了一个列，最后WHERE 再按那个列来限定
SELECT * AS A FROM TB WHERE CASE WHEN 1=1 THEN 1 ELSE 0 END =1
等价于
SELECT * FROM （SELECT * ，CASE WHEN 1=1 THEN 1 ELSE 0 END AS A FROM TB） T WHERE A=1


...

			
		

	





	

		

			

				
					
在where条件中使用CASE WHEN 语句

				
			

			

				

					weixin_30514745的博客
				

				

					06-15
					
					5353
					
				

			

		

		

			
				
CREATE TABLE TB_Test_Report
(
id  int identity,
stateid int,
userid  int,
username varchar(64)
)

go

INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (20,1,'a')
INSERT INTO TB...

			
		

	





	

		

			

				
					
where语句后跟case语句

				
			

			

				

					qq_43985303的博客
				

				

					02-08
					
					3852
					
				

			

		

		

			
				
补充：当使用 B.name = 时，将不会出现以上问题，也就是如下写法也正确。正确写法：将B.name in 放到CASE END 中。在where后面使用case when的说明。

			
		

	





	

		

			

				
					
where条件中使用case when来实现不同列的join

				
			

			

				

					ciliting2867的博客
				

				

					04-18
					
					750
					
				

			

		

		

			
				
1. at_naizan表为奶站表，其中含有该省ID，地区ID,县市ID。2. ENFORCE为执法机构表，其中包含了该执法机构的执法范围，scope字段，有省级、地级、县级3. ENFORCEMAN为执法人员表，该表...

			
		

	





	

		

			

				
					
where后面case-when的用法

				
			

			

				

					Min_Monk的博客
				

				

					12-11
					
					1万+
					
				

			

		

		

			
				
准备测试数据，建表语句
	
drop table test_table;
create table test_table
(
  col_a        NUMBER,
  col_b        NUMBER,
  col_c        VARCHAR2(5)
)

insert into test_table values(2, 1, '>');
insert into test...

			
		

	





	

		

			

				
					
case when和where

				
			

			

				

					weixin_46767998的博客
				

				

					05-21
					
					1206
					
				

			

		

		

			
				
注意:SQL语句中如果null换成0,就有问题了.
1! =3 =2


SELECT T.PERIOD_CODE,  T.LEAVE_FLAG,T.EMPL_STATUS,
COUNT(case when T.EMPL_STATUS=‘在职’ THEN 1 ELSE 0 END) AS TOTAL1
FROM dm_hr_empl_detail T
WHERE PERIOD_CODE=‘2021-05’


SELECT T.PERIOD_CODE,  T.LEAVE_FLAG,T.EMPL_STATUS

			
		

	





	

		

			

				
					
selserver在where中怎么写case when

				
			

			

				

					07-15
				

			

		

		

			
				
CASE WHEN语句可以用于在WHERE子句中进行条件判断。如果你想在WHERE子句中使用CASE WHEN，可以按照以下格式编写：

```sql
SELECT column1, column2, ...
FROM table
WHERE 
    CASE 
        WHEN condition1 THEN expression1
        WHEN condition2 THEN expression2
        ...
        ELSE expressionN
    END;
```

在上面的例子中，condition1、condition2等是条件表达式，expression1、expression2等是相应条件满足时的结果表达式。

请注意，CASE WHEN语句在WHERE子句中的使用是有限制的，因为它只能返回一个值。如果你需要更复杂的条件判断，可能需要考虑使用其他方式来组合条件。

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值