SQL语句问题，结合4个表以获得要显示的正确信息

Question

我很难弄清楚这件事。我正在使用MS 2008，并试图将未计费表和账单表中的总技术时数相加，并将它们分组。如果账单报表无效，我还需要根据第三个表的报表头排除计费小时的行。然后我需要从第四张桌子上找出技术人员的名字。未开票的表是SCQReportLabors，它列出了所有的劳动记录。SCReportLabors表列出了所有计费的劳动记录。SCReports是报告头，我需要知道VoidID列是否为null。ShAgents表保存了技术名称。下面是我目前所拥有的，我知道它不起作用，但这是我到目前为止所得到的。对于输出数据，我非常喜欢TotalHours列和TechName列。如果我最终得到的是账单总数、未收费总额和技术栏，我也可以接受这一点。我已经盯着这个看了一段时间了，需要一些观点和建议。提前感谢您的帮助。

SELECT a.TotalHours, c.PrefFullName AS TechName
    FROM (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID 
        FROM SCReportLabors
        LEFT OUTER JOIN SCReports d ON a.ReportID = d.ReportID
    WHERE d.VoidID IS NULL
    GROUP BY TechnicianID) a
    JOIN (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID
      FROM SCQReportLabors
      GROUP BY TechnicianID) b
ON a.TechnicianID = b.TechnicianID
    LEFT OUTER JOIN ShAgents c ON a.TechnicianID = c.AgentID
ORDER BY c.PrefFullName

Answer 1

此查询应捕获所有数据并处理空小时。

SELECT
    A.PrefFullName AS 'TechName',
    (ISNULL(B.TotalHours, 0) + ISNULL(C.TotalHours, 0)) AS 'Total Hours'
FROM
    ShAgents AS A
    LEFT OUTER JOIN
        (
            SELECT 
                (
                    SUM(ISNULL(LaborHours, 0)) + 
                    SUM(ISNULL(OvertimeHours, 0)) + 
                    SUM(ISNULL(TravelHours, 0))
                ) AS 'TotalHours', 
                TechnicianID 
            FROM 
                SCReportLabors AS tA
                LEFT OUTER JOIN SCReports AS tB
                    ON tA.ReportID = tB.ReportID
            WHERE 
                tB.VoidID IS NULL
            GROUP BY 
                TechnicianID
        ) AS B
        ON A.AgentID = B.TechnicianID
    LEFT OUTER JOIN
        (
            SELECT 
                (
                    SUM(ISNULL(LaborHours, 0)) + 
                    SUM(ISNULL(OvertimeHours, 0)) + 
                    SUM(ISNULL(TravelHours, 0))
                ) AS 'TotalHours', 
                TechnicianID
            FROM 
                SCQReportLabors
            GROUP BY 
                TechnicianID
        ) AS C
        ON A.AgentID = C.TechnicianID
ORDER BY 
    A.PrefFullName

Answer 2

我将这两个主要查询设为UNION查询，并为VOID列介绍了联接。

SELECT SQ1.TotalHours, SHA.PrefFullName AS TechName
FROM
(
SELECT SUM(TOTALHOURS) AS TotalHours, TechnicianID
    (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID 
    FROM SCReportLabors L
    JOIN SCReports RPTS
    ON L.ReportID = RPTS.ReportID
    WHERE RPTS.VoidID IS NULL
    GROUP BY TechnicianID)
    UNION ALL
    (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID
     FROM SCQReportLabors
     GROUP BY TechnicianID
     )
) SQ
GROUP BY TechnicianID
JOIN ShAgents SHA
ON SQ.TechnicianID = SHA.AgentID
ORDER BY SHA.PrefFullName