sql - 在 3 个查询之间加入和计算

Question

我有以下 3 个 sql 查询，具有不同的条件和规则，但输出相同：

查询1：

Select CONVERT(char(10), DatePayment, 120) PaymentDate, IsNull(SUM(UnitPrice * Quantity), 0) POSAmount 
From POSSales P 
Inner Join POSSalesDetails PD On P.ID = PD.SalesID 
Inner Join Payments PAY On P.PaymentID = PAY.ID 
Inner Join POSItems PDI On PD.ItemID = PDI.ID 
Where DatePayment >= '2019-04-01 08:00' And 
DatePayment <= '2019-04-30 08:00'
group by CONVERT(char(10), DatePayment, 120)
order by CONVERT(char(10), DatePayment, 120)

样本输出

----------------------------
| PaymentDate  |  POSAmount|
---------------------------
| 2019-05-01   |    510.00 |
| 2019-05-02   |    120.00 |
| ........     | ........  |
----------------------------

查询2：

Select CONVERT(char(10), DatePayment, 120) PaymentDate, 
IsNull(SUM(TotalAmount), 0) ShowerAmount 
From ShowerBookings S 
Inner Join ShowerPayments SP On S.ID = SP.BookingID 
Inner Join Payments PAY On SP.PaymentID = PAY.ID 
Where 
DatePayment >= '2019-04-01 08:00' And 
DatePayment <= '2019-04-30 08:00' 
group by CONVERT(char(10), DatePayment, 120) 
order by CONVERT(char(10), DatePayment, 120)

样本输出

----------------------------
| PaymentDate  |ShowerAmount|
----------------------------
| 2019-05-01   |    220.00  |
| 2019-05-02   |    310.00  |
| ........     | ........   |
-----------------------------

查询3：

Select CONVERT(char(10), DatePayment, 120) PaymentDate, 
IsNull(SUM(TotalAmount), 0) LockerAmount 
From LockerBookings L 
Inner Join LockerPayments LP On L.ID = LP.BookingID 
Inner Join Payments PAY On LP.PaymentID = PAY.ID 
Where 
DatePayment >= '2019-04-01 08:00' And 
DatePayment <= '2019-04-30 08:00' 
group by CONVERT(char(10), DatePayment, 120) 
order by CONVERT(char(10), DatePayment, 120)

样本输出

----------------------------
| PaymentDate  |LockerAmount|
----------------------------
| 2019-05-01   |    150.00  |
| 2019-05-02   |    180.00  |
| ........     | ........   |
-----------------------------

和我的最终输出.. 我想求和（Amount1+Amount2+Amount3）并按 PaymentDate 分组。

我已经尝试了以下但它不起作用

select Q1.PaymentDate, sum(Q1.Amount1 + Q2.Amount2 + Q3.Amount3) as TotalAmount
from (Query1) as Q1, (Query2) as Q2, (Query3) as Q3

我期待这样的结果：

预期产出

----------------------------
| PaymentDate  |  TAmount  |
---------------------------
| 2019-05-01   |    880.00 |
| 2019-05-02   |    610.00 |
| ........     | ........  |
----------------------------

如果可以这样工作，生活会容易得多-_-感谢您的帮助和助手。谢谢

注意我更新了查询以便更好地理解为什么我需要所有的金额。我计划不更改主查询，因为它将被其他功能使用。所以我需要提出新的查询来获得所有 3 个查询的总数..

标签： sql

Answer 1

GROUP BY您的 3 个查询很陌生，在没有任何聚合函数的情况下使用它可以成功运行吗？

您预期的查询应具有如下结构：

SELECT col1, SUM(col2) AS TAmount  
FROM
(
    query_1  --SELECT col1, col2, ...
    UNION ALL
    query_2  --SELECT same number of columns as query_1. 
             -- Name of columns could be any name, but using UNION ALL, in output the DBMS will only use names of columns in query_1 
    UNION ALL
    query_3  --SELECT same number of columns as query_1. 
)
GROUP BY col1
ORDER BY col1;

因此，对于您编辑的问题，最终查询将是：

SELECT PaymentDate, SUM(amount) AS TAmount  
FROM
(
    SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, 
        IsNull(SUM(UnitPrice * Quantity), 0) AS amount
    FROM POSSales P 
    INNER JOIN POSSalesDetails PD ON P.ID = PD.SalesID 
    INNER JOIN Payments PAY ON P.PaymentID = PAY.ID 
    INNER JOIN POSItems PDI ON PD.ItemID = PDI.ID 
    WHERE DatePayment >= '2019-04-01 08:00' 
        AND  DatePayment <= '2019-04-30 08:00'
    GROUP BY CONVERT(CHAR(10), DatePayment, 120)

    UNION ALL

    SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, 
        IsNull(SUM(TotalAmount), 0) --AS amount
    FROM ShowerBookings S 
    INNER JOIN ShowerPayments SP ON S.ID = SP.BookingID 
    INNER JOIN Payments PAY ON SP.PaymentID = PAY.ID 
    WHERE 
        DatePayment >= '2019-04-01 08:00' 
        AND  DatePayment <= '2019-04-30 08:00' 
    GROUP BY CONVERT(CHAR(10), DatePayment, 120) 

    UNION ALL

    SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, 
        IsNull(SUM(TotalAmount), 0) --AS amount
    FROM LockerBookings L 
    INNER JOIN LockerPayments LP ON L.ID = LP.BookingID 
    INNER JOIN Payments PAY ON LP.PaymentID = PAY.ID 
    WHERE 
        DatePayment >= '2019-04-01 08:00' 
        AND  DatePayment <= '2019-04-30 08:00' 
    GROUP BY CONVERT(CHAR(10), DatePayment, 120) 
)
GROUP BY PaymentDate
ORDER BY PaymentDate;