如何将三个查询合并成一个？

Question

我有三个查询，最终可以得到每日财务信息。我想将我需要的列合并为一个输入。我尝试使用派生表，但它非常混乱且无法正常工作。是否有人有任何建议，可以告诉我如何将这三个查询组合成一个输出，并显示在一行上？

以下是我的查询：

SELECT        ReportCategoryID, ReportCategoryName, PrintOrder, QTY, NetAmt, customers, Average
FROM            dbo.fRevenueSummary(@startdate, @enddate) AS fRevenueSummary_1

第二个查询：

SELECT        COUNT(*) AS count, SUM(TicketsDetails.dblTotal) AS Merch_Total
FROM            TicketsDetails INNER JOIN
                         Surcharges ON TicketsDetails.Reference_ID = Surcharges.Surcharge_ID INNER JOIN
                         SurchargesTypes ON Surcharges.lSurchargeTypeID = SurchargesTypes.lSurchargeTypeID
WHERE        (TicketsDetails.dtCreated BETWEEN @startdate AND @enddate) AND (SurchargesTypes.lSurchargeTypeID = '2') AND (TicketsDetails.bPaid = 1) AND 
                         (TicketsDetails.Detail_Type_ID = 3) AND (NOT (TicketsDetails.sDescription = N'TIP'))

第三个查询：

SELECT        SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
FROM            TimeClock
WHERE        (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '1') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '2') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '3') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '4') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '5') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '6') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '7')

- Shmewnix

---

请问您能否解释一下期望的输出结果以及表“TimeClock”、“TicketsDetails”、“Surcharges”和“SurchargesTypes”之间的关系是什么？ - Mahmoud Gamal

Answer 1

最简单的方法就像这样。

SELECT *
FROM
(
    SELECT        ReportCategoryID, ReportCategoryName, PrintOrder, QTY, NetAmt, customers, Average
    FROM            dbo.fRevenueSummary(@startdate, @enddate) AS fRevenueSummary_1
)
,
(
    SELECT        COUNT(*) AS count, SUM(TicketsDetails.dblTotal) AS Merch_Total
    FROM            TicketsDetails INNER JOIN
                         Surcharges ON TicketsDetails.Reference_ID = Surcharges.Surcharge_ID INNER JOIN
                         SurchargesTypes ON Surcharges.lSurchargeTypeID = SurchargesTypes.lSurchargeTypeID
    WHERE        (TicketsDetails.dtCreated BETWEEN @startdate AND @enddate) AND (SurchargesTypes.lSurchargeTypeID = '2') AND (TicketsDetails.bPaid = 1) AND 
                         (TicketsDetails.Detail_Type_ID = 3) AND (NOT (TicketsDetails.sDescription = N'TIP'))
)
,
(
    SELECT        SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
    FROM            TimeClock
    WHERE        (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '1') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '2') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '3') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '4') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '5') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '6') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '7')
)

但如果它们之间没有关系，你将返回一个笛卡尔积。

我认为没有办法在没有笛卡尔积的情况下组合3个不同的查询。

- Marc

---

这是一个笛卡尔积。我认为没有其他方法可以将3个不同的查询组合在一起。 - Marc

Answer 2

如果你想要一个丑陋、混乱的解决方案，你可以在每个语句周围加上括号，然后将每个语句视为巨大的 JOIN 中的表（类似于 Marc 所示的内容）。

如果你想要一个好的解决方案，你需要描述你实际想要做什么。如果不知道你的数据和实际任务，我们无法优化你的代码。

- dan1111

Answer 3

这不是对你问题的回答，但是了解到你的第三个查询可以大大简化你的思考：

SELECT  SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
FROM    TimeClock
WHERE   (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = IN('1', '2', '3', '4', '5', '6', '7'))

- James Hill