Sql查询如果空，我想要返回值0 [英] Sql Query If Null I Want Return Value 0

问题描述

全部



我遇到这个查询的问题，我想要这个查询当返回空时我想要填充值0，



我的查询是：

 选择  distinct  sm.EmpCodeC，sm.EmpNameC，（ select  count（UserId）来自 CanteenLogs 其中 TimeEntry   '  01 / Oct / 2014 09:00:00 AM' 和 '  01 / Oct / 2014 10:00:00 AM' 和 userId = '  20'） as  '  BreakFast' 来自 CanteenLogs  as  rd  inner   join  StaffMaster  as  sm  on  rd.UserID = sm.UserIdN 和 rd.UserID = '  20' 

这里我传递值20，如果userid = 20返回空，我想要BreakFast列为值0

解决方案

< blockquote>使用 COALESCE [ ^ ]函数，用传递的值替换 NULL 。

  SELECT   COALESCE （ NULL ， 0 ）  -   返回0  

查看此

 选择  distinct  
 sm.EmpCodeC，sm.EmpNameC，
 Isnull（（ select  count（UserId）来自 CanteenLogs 其中 TimeEntry   ' < span class =code-string> 01 / Oct / 2014 09:00:00 AM' 和  '  01 / Oct / 2014 10:00:00 AM' 和 userId = '  20'）， 0 ） as  '  BreakFast' 
  FR om  CanteenLogs  as  rd  inner   join  StaffMaster  as  sm  on  rd.UserID = sm.UserIdN 和 rd.UserID = '  20  

当没有记录返回时

 选择  distinct  
 sm.EmpCodeC，sm.EmpNameC，
 Isnull（（选择计数（UserId）来自 CanteenLogs 
 其中​​ TimeEntry   '  01 / Oct / 2014 09:00:00 AM' 和 '  01 / Oct / 2014 10:00:00 AM' 和 userId = '  20'）， 0 ） as  '  BreakFast' 
  into  #tempfile 
 来自 CanteenLogs rd  inner  加入 StaffMaster sm   rd.UserID = sm.UserIdN 和 rd.UserID = '  20' 
 
  IF  存在（选择  top   1  * 来自 #tempfile）
 选择 * 来自 Outputdata 
  else  
 选择 ' ' EmpCodeC，' ' EmpNameC， 0  BreakFast 

Hi, All

i'm facing problem with this query, i want this query when return empty then i want fill value 0,

My Query Is:

select distinct sm.EmpCodeC,sm.EmpNameC,(select count(UserId) from CanteenLogs where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20') as 'BreakFast' from CanteenLogs as rd inner join StaffMaster as sm on rd.UserID=sm.UserIdN and rd.UserID='20'

Here i'm passing value 20, if userid=20 returns empty i want BreakFast column as value 0

解决方案

Use COALESCE[^] function, which replaces NULL with passed value.

SELECT COALESCE(NULL, 0) --returns 0

check this

select distinct 
   sm.EmpCodeC,sm.EmpNameC,
  Isnull((select count(UserId) from CanteenLogs where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20'),0) as 'BreakFast' 
from CanteenLogs as rd inner join StaffMaster as sm on rd.UserID=sm.UserIdN and rd.UserID='20

and this when no record returned

select distinct
   sm.EmpCodeC,sm.EmpNameC,
  Isnull((select count(UserId) from CanteenLogs
                 where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20'),0) as 'BreakFast'
 into #tempfile
from CanteenLogs rd inner join StaffMaster sm on rd.UserID=sm.UserIdN and rd.UserID='20'

IF Exists (select top 1 * from #tempfile)
select * from Outputdata
else
select '' EmpCodeC ,'' EmpNameC ,0 BreakFast

这篇关于Sql查询如果空，我想要返回值0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！