如何在sqlserver中使用case Statemet获取Null值? [英] how do get Null value using case Statemet in sqlserver?
本文介绍了如何在sqlserver中使用case Statemet获取Null值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想使用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屋!
查看全文