如何使用“或” SQL中的case语句中的子句 [英] How to use "or" clause in case statement in SQL

问题描述

我有一个名为master_unittype的表，其中有uinttypeid和typename。

喜欢

 unittypeid -------- ----- typename 
 1 ------------------------- kg 
 2 -------- ----------------- pc 

现在我必须使用条件从此表中选择unittypeid，typename。 />


另一个查询返回'kg'，'gm'，'pc'等我正在存储一个名为

<$的变量c $ c> @usageunitName 。



现在如果@ usageunitName ='kg'，我必须从master_unittype中选择unittypeid，typename typename ='kg'

和

如果@ usageunitName ='pc'，我必须从master_unittype中选择unittypeid，typename，其中typename ='kg'或typename ='个人计算机'



我的尝试：

 声明  @ usageunitName   va rchar （ 50 ）; 
 
 选择 @ usageunitName = typename 来自 master_product 内部  join  master_unit_type   master_product.productUnitTypeId = master_unit_type.unittypeid 其中 productid = 1; 
 
 
 选择 unittypeid，typename 来自 master_unit_type  where （（ case    @ usageunitName = '  kg' 然后（typename = '  kg'）
   @ usageunitName = < span class =code-string>'  pc' 然后（typename = '  kg' 或 typename = '  pc'）
  else  typename = < span class =code-string>' ' 
  zh-CN d ））; 

解决方案

不需要 CASE 声明。请尝试这样做

选择unittypeid，typename from master_unit_type 
 where（@usageunitName ='kg'AND typename ='kg'）
 OR（@usageunitName ='pc'AND（typename ='pc'OR typename ='kg'））
 

尝试：

  WHERE （ @ usageunitName  = '  kg'  AND  typename =  '  kg'）
  OR （ @usageunitName  = '  pc'  AND （typename = '  kg' 或 typename = '  pc'））
  OR （typeName = '  ）

I have a table called master_unittype where uinttypeid and typename is there.
like

unittypeid-------------typename
1-------------------------kg
2-------------------------pc

now I have to select unittypeid,typename from this table using a condition.

some another query is returning 'kg','gm','pc' etc and I'm storing it a variable named
@usageunitName.

Now if @usageunitName='kg', i have to select unittypeid,typename from the master_unittype where typename='kg'
and
if @usageunitName='pc', i have to select unittypeid,typename from the master_unittype where typename='kg' or typename='pc'

What I have tried:

declare @usageunitName varchar(50);

select @usageunitName=typename from master_product inner join master_unit_type on master_product.productUnitTypeId=master_unit_type.unittypeid where productid=1;

 
 select unittypeid,typename from master_unit_type where ((case when @usageunitName='kg' then (typename='kg')
      when @usageunitName='pc' then (typename='kg' or typename='pc')
      else typename=''
 end));

解决方案

There is no need for a CASE statement at all. Try this instead

select unittypeid,typename from master_unit_type
where (@usageunitName  ='kg' AND typename = 'kg')
   OR (@usageunitName  ='pc' AND (typename = 'pc' OR typename = 'kg'))

Try:

WHERE (@usageunitName = 'kg' AND typename = 'kg')
   OR (@usageunitName = 'pc' AND (typename = 'kg' OR typename = 'pc'))
   OR (typeName = '')

这篇关于如何使用“或” SQL中的case语句中的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！