带有两个sum语句的SQL多表查询

Question

优秀的sql专家！:)我的老板想要他的员工工作的月/年/日报告。我认为我需要的大多数SQL查询，但最后一个超出了我的理解。

我有几个表:人员、服务、计划和ProvidedServices。表计划包含每个服务的每个人的月计划。因此，我需要一个查询，返回服务的总价值，在特定时间段内，每个人在特定服务上提供服务，而在同一服务期间，计划工作量的汇总值。现在我有了这样的东西：

SELECT  SUM(BoRRenderedServices.ServiceCount), 
       BoREmployee.Name, 
       BoRServices.Service,
       SUM(BoRTargets.Amount)
FROM BoRRenderedServices, BoREmployee, BoRServices, BoRTargets
WHERE (BoRTargets.EmployeeID = BoREmployee.ID) 
AND (BoRTargets.ServiceID = BoRServices.ID) 
AND (BoRRenderedServices.Date BETWEEN '2014-1-1' AND '2014-9-19') 
AND (BoREmployee.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
AND (BoRRenderedServices.EmployeeID = BoREmployee.ID) 
AND (BoRRenderedServices.ServiceID = BoRServices.ID) 
AND (BoRRenderedServices.ServiceID = '0fbf68bf-ace8-4ecb-ba07-7049046c0215') 
GROUP BY BoREmployee.Name, 
       BoRServices.Service

但是这个查询做了双和，它两次总结了所提供的服务数量，对于计划也是如此。我的意思是，每次它创建提供的服务，处理WHERE的条件--它也总结计划，而不是只在第一个sum语句中添加提供的服务。

人1，在8月1日提供三次Service1，在8月3日提供一次，在7月4日提供4次。Person1 8月份的计划是3项，7月份是4项。

期望产出：

--------------Plan Fact Service1 1----------1 Service2 1----------2 Service3 1----------3

实际产出：

--------------Plan Fact Service1 3----------3 Service2 3----------6 Service3 3----------9

Answer 1

我改变了查询。您可以创建视图或sql数据库。

检查SqlFiddle

with tbl1 
as
(
  SELECT 
        b.EmployeeId
       ,b.ServiceId
       ,c.Service
       ,d.Name
       ,Sum(b.Amount) Amount
FROM 
    BoRTargets b
        inner join BoRServices c
    on b.ServiceId = c.Id
        inner join BoREmployee d
    on b.EmployeeId = d.Id
WHERE (b.Date BETWEEN '2014-09-01' AND '2014-09-26') 
 AND (d.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
GROUP BY b.EmployeeId, b.ServiceId, c.Service, d.Name
)
,

tbl2
as
(
  SELECT 
      a.EmployeeId
     ,a.ServiceId
     ,c.Service
     ,d.Name
     ,SUM(a.ServiceCount) ServiceCount
FROM 
    BoRRenderedServices a 
        inner join BoRServices c
    on a.ServiceId = c.Id
        inner join BoREmployee d
    on a.EmployeeId = d.Id
WHERE EXISTS(
    SELECT 1 
    FROM BorTargets b 
     where (a.EmployeeId = b.EmployeeId and a.ServiceId = b.ServiceId)
     AND (b.Date BETWEEN '2014-09-01' AND '2014-09-26'))
 AND (d.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
GROUP BY a.EmployeeId, a.ServiceId, c.Service, d.Name
)

select coalesce(a.Service, b.Service) Service, coalesce(a.Name, b.Name) Name, a.Amount, b.ServiceCount
from tbl1 a full join tbl2 b
 on a.EmployeeId = b.EmployeeId 
 and a.ServiceId = b.ServiceId