同列的多和

Question

我需要这个select查询来根据不同的where子句返回同一列的和。基本上只是寻找合并这些多个select查询的方法。请看下面的内容。任何帮助都是非常感谢的！

DECLARE @prEndDate  smalldatetime
SET @prEndDate='2014-01-05'

SELECT  Employee, SUM(Amount) as Deduction
FROM bPRDT
WHERE PREndDate=@prEndDate  
AND EDLCode=100 AND EDLType='D'  
GROUP BY Employee

SELECT  Employee, SUM(Amount) as DeductionPlus
FROM bPRDT
WHERE PREndDate=@prEndDate  
AND EDLCode=101 AND EDLType='D'  
GROUP BY Employee

SELECT  Employee, SUM(Amount) as Match
FROM bPRDT
WHERE PREndDate=@prEndDate  
AND EDLCode=600 AND EDLType='L'  
GROUP BY Employee

SELECT  Employee, SUM(Amount) as MatchPlus
FROM bPRDT
WHERE PREndDate=@prEndDate  
AND EDLCode=601 AND EDLType='L'  
GROUP BY Employee

终于修好了。结果是这样的：

DECLARE @prEndDate smalldatetime
SET @prEndDate ='2013-12-29'
SELECT  
REPLACE(c.SSN,'-',''),
SUM(CASE WHEN a.EDLType='D' AND a.Amount > 0 AND (a.EDLCode=100 OR a.EDLCode=101) THEN a.Amount ELSE 0 END) AS Deferral,
SUM(CASE WHEN a.EDLType='L' AND a.Amount > 0 AND (a.EDLCode=600 OR a.EDLCode=601) THEN a.Amount ELSE 0 END) AS EmployerMatch,
SUM(CASE WHEN a.EDLType='D' AND a.Amount > 0 AND (a.EDLCode=100 OR a.EDLCode=101) THEN a.SubjectAmt ELSE 0 END) AS Compensation415,
SUM(CASE WHEN a.EDLType='D' AND a.Amount > 0  AND (a.EDLCode=100 OR a.EDLCode=101) THEN a.SubjectAmt ELSE 0 END) AS PlanFullYearCompensation,
SUM(CASE WHEN a.EDLType='E' AND a.Amount > 0 AND (a.EDLCode=1 OR a.EDLCode=2 OR a.EDLCode=3)  THEN a.Hours ELSE 0 END) AS PlanHours
FROM  bPRDT a  
JOIN (SELECT DISTINCT SSN, Employee FROM bPREH) c ON a.Employee=c.Employee    
WHERE a.PREndDate=@prEndDate 

GROUP BY c.SSN  
ORDER BY Deferral DESC, c.SSN ASC  

Answer 1

您可以使用UNION或CASE WHEN子句。

SELECT  
      Employee, 
      SUM(CASE WHEN EDLCode=100 AND EDLType='D' THEN Amount ELSE 0 END) as Deduction,
      SUM(CASE WHEN EDLCode=101 AND EDLType='D' THEN Amount ELSE 0 END) as DeductionPlus
      SUM(CASE WHEN EDLCode=600 AND EDLType='L' THEN Amount ELSE 0 END) as Match
      SUM(CASE WHEN EDLCode=601 AND EDLType='L' THEN Amount ELSE 0 END) as MatchPlus
FROM  bPRDT
WHERE PREndDate=@prEndDate 
GROUP BY Employee

Answer 2

您可以将条件从WHERE子句移到CASE语句中，因此您只需要将感兴趣的行加在一起：

DECLARE @prEndDate  smalldatetime
SET @prEndDate='2014-01-05'

SELECT   Employee, 
         SUM(CASE WHEN EDLCode=100 AND EDLType='D' THEN amount ELSE 0 END) 
           AS Deduction,
         SUM(CASE WHEN EDLCode=101 AND EDLType='D' THEN amount ELSE 0 END)
           AS DeductionPlus,
         SUM(CASE WHEN EDLCode=600 AND EDLType='L' THEN amount ELSE 0 END)
           AS Match,
         SUM(CASE WHEN EDLCode=601 AND EDLType='L' THEN amount ELSE 0 END)
           AS MatchPlus
FROM     bPRDT
WHERE    PREndDate = @prEndDate  
GROUP BY Employee

Answer 3

按员工、EDLCode、EDLType分组，而不仅仅是员工。