如何在sql server 2008中的case语句中使用sum [英] How use sum in case statement in sql server 2008

问题描述

大家好



这里我的概率我不知道怎么总结以防万一



my代码：

Hi All

Here my prob i dont know how to sum in case satement

my code :

SELECT DISTINCT CASE WHEN(ReaCodeC in ('PRS'))
THEN (select count(EmpCodeC) from ClientDeployment where Dated between '2015-03-01' and '2015-03-30' and ShiftGroupC='Night' and EmpCodeC='1288')
WHEN (ReacodeC NOT IN ('PRS')) then '0' end from Daily where ClockDateD between '2015-04-01' and '2015-04-30' and ShiftGroupCodeC = 'Night' and EmpCodeC in ('1288')

输出：



0

12（返回两排）



这里我想加0 + 12 = 12（想要退回单行）



怎么做这个



谢谢！

output :

0
12 (return two row)

here i want to add 0+12=12(want to return single row)

how to make this

Thanks !

推荐答案

这将工作

This will work

DECLARE @ClientDeployment AS TABLE (EmpCodeC NVARCHAR(30),Dated DATETIME,ShiftGroupC NVARCHAR(30))

DECLARE @Daily AS TABLE (ReaCodeC  NVARCHAR(30),ClockDateD DATETIME,ShiftGroupCodeC NVARCHAR(30),EmpCodeC NVARCHAR(30))



INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-10,'Night')
INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-9,'Night')
INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-8,'Night')
INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-7,'Night')
INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-6,'Night')
INSERT INTO @ClientDeployment (EmpCodeC,Dated,ShiftGroupC)
					  VALUES ('1288',GETDATE()-5,'Night')
					  
					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-10,'Night','1288')	 

					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-9,'Night','1288')	
    
    
					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-8,'Night','1288')	
    
    
 					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-7,'Night','1288')	

					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-6,'Night','1288')	
    
    					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('PRS',GETDATE()-5,'Night','1288')	
    
    					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('MRS',GETDATE()-10,'Night','1288')	 

					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('MRS',GETDATE()-9,'Night','1288')	
    
    
					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('MRS',GETDATE()-8,'Night','1288')	
    
    
 					  
INSERT INTO @Daily (ReaCodeC,ClockDateD,ShiftGroupCodeC,EmpCodeC)		
    VALUES ('MRS',GETDATE()-7,'Night','1288')	

					  



SELECT TOP 1 CASE WHEN(ReaCodeC IN ('PRS'))
                          THEN (SELECT COUNT(EmpCodeC) FROM @ClientDeployment 
								WHERE Dated BETWEEN '2015-05-20' AND '2015-05-30' AND ShiftGroupC='Night' AND EmpCodeC='1288')
								ELSE '0' END FROM @Daily 
WHERE ClockDateD BETWEEN '2015-05-20' AND '2015-05-30' AND ShiftGroupCodeC = 'Night' AND EmpCodeC IN ('1288')

Out put：



6

Out put :

6

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