如何在sqlserver中使用case Statemet获取Null值？ [英] how do get Null value using case Statemet in sqlserver?

问题描述

我想使用case语句获取null值。

我的sql查询如下

 选择 UserID，ProcessDate，Leave =（
  case  
 当 firsthalf = '  PR' 和 secondhalf = '  PR' 然后 '  0' 
 何时 firsthalf = '  WO' 和 secondhalf = '  WO' 然后 '  0' 
 当 firsthalf = '  AB' 和 secondhalf = '  AB' 然后 '  1' 
  when  firsthalf    null  和 secondhalf    null  然后 '  1' 
   firsthalf = '  PR' 和 secondhalf = '  AB' 然后 '  0.5' 
 何时 firsthalf = '  AB' 和 secondhalf = '  PR' 然后 '  0.5' 
  when  firsthalf = '  IN' 和 secondhalf    null  然后 '  0.5' 
   firsthalf = '  IN' 和 secondhalf = '  AB' 然后 '  0.5' 
 何时 firsthalf = '  IN' 和 secondhalf = '  Out' 然后 '  0.5' 
   firsthalf = < span class =code-string>'  Out' 和 secondhalf = '  IN' 然后 '  0.5' 
  end ）
，d atename（月，processdate） as 月份，datepart（yyyy，processdate） as 年
 < span class =code-keyword>来自 Accounts_DailyAttendance 其中 UserID = 1003 和 datename（ month，processdate）= '  March' 和 datepart（yyyy，processdate）= '  2014' 和 firsthalf<> '  PR' 和 secondhalf<> ; '  PR' 和 firsthalf<> ; '  WO' 和 secondhalf<> ; '  WO' 

当我运行上面的sql查询时，没有输出null值。



所以我想要null值数据也可以在生物识别条目中获得processdate。



请帮助我在上面的sql查询中犯了什么错误。

解决方案

  CASE  
  WHEN  firsthalf  IS   NULL   AND  secondhalf  IS   NULL  然后  NULL  
  END  

当然，您可以使用除AND之外的任何其他逻辑运算符 - 这取决于您的需求。

I want to get the null value output using case statement.
My sql query as follows

select UserID, ProcessDate,Leave=(
  case 
    when firsthalf = 'PR' and  secondhalf = 'PR' then '0' 
    when  firsthalf = 'WO'  and  secondhalf = 'WO'   then  '0'
    when  firsthalf = 'AB'  and  secondhalf = 'AB'   then  '1'
    when  firsthalf is null and  secondhalf is null  then  '1'
    when  firsthalf = 'PR'  and  secondhalf = 'AB'   then  '0.5'
    when  firsthalf = 'AB'  and  secondhalf = 'PR'   then  '0.5'
    when  firsthalf = 'IN'  and  secondhalf is null  then  '0.5'
    when  firsthalf = 'IN'  and  secondhalf = 'AB'   then  '0.5'
    when  firsthalf = 'IN'  and  secondhalf = 'Out'  then  '0.5'
    when  firsthalf = 'Out' and  secondhalf = 'IN'   then  '0.5' 
  end)
,datename(month,processdate) as Months,datepart(yyyy,processdate) as Years
 from Accounts_DailyAttendance where UserID=1003 and datename(month,processdate) = 'March' and datepart(yyyy,processdate)= '2014' and firsthalf <> 'PR'  and  secondhalf <> 'PR' and firsthalf <> 'WO'  and  secondhalf <> 'WO'

When i run the above sql query null value output is not came.

so i want null value data also get processdate in biometric entry.

please help me what is the mistake i made in my above sql query.

解决方案

CASE
  WHEN firsthalf IS NULL AND secondhalf IS NULL then NULL
END

Of course you can use any other logical operator than AND - it depends on your needs.

这篇关于如何在sqlserver中使用case Statemet获取Null值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！