MySQL合并兩個不相關表的結果-有解無憂

我正在嘗試將兩個查詢結果合并為一個：查詢 1 和結果：

    SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblFeesPaid.Fees_Paid) As 'Total Fees' 
    FROM tblFeesPaid 
        INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
    WHERE Year(tblFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblFeesPaid.Pay_Date);

結果

    Month    Total Fees 
    January  162000.00
    February 69000.00
    March    146926.00

查詢 2 和結果

    SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblTransFeesPaid.TransFee_Paid) As 'Transport Fees' 
    FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date);

結果

    Month Transport Fees
    March 7000.00

有人可以幫助我使用正確的語法，我將獲得如下結果：

預期成績：

 Month    Total Fees  Transport Fees
 January  162000.00
 February 69000.00
 March    146926.00   7000.00

uj5u.com熱心網友回復：

SELECT Z.Month, sum(Z.TotalFees) As 'Total Fees', sum(Z.TransportFees) As 'Transport Fees'
FROM
(
SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month',
       SUM(tblFeesPaid.Fees_Paid) As 'TotalFees',
       0 As 'TransportFees'
FROM tblFeesPaid 
INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
WHERE Year(tblFeesPaid.Pay_Date)=2022 
GROUP BY month(tblFeesPaid.Pay_Date)
UNION
SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month',
       0 As 'TotalFees',
       SUM(tblTransFeesPaid.TransFee_Paid) As 'TransportFees' 
FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date)) Z
GROUP BY Z.Month;

uj5u.com熱心網友回復：

首先，確保兩個查詢回傳相同數量的列（在第一個查詢中添加 0 作為運輸費用，在第二個查詢中添加 0 作為總費用）；

然后，您將查詢聯合起來，得到一個大的結果集。

之后按月分組，得到 MAX(Total Fees) 和 MAX(Transport Fees)。

這樣的東西會起作用嗎？

    SELECT Month, MAX(`Total Fees`), MAX(`Transport Fees`) FROM (

    (
        SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month', 
                SUM(tblFeesPaid.Fees_Paid) As 'Total Fees',
                0 AS 'Transport Fees'
        FROM tblFeesPaid 
        INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
        WHERE Year(tblFeesPaid.Pay_Date)=2022 
        GROUP BY month(tblFeesPaid.Pay_Date)
    ) t1

    UNION

    (
        SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month', 
                0 AS 'Total Fees',
                SUM(tblTransFeesPaid.TransFee_Paid) As 'Transport Fees' 
        FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
        WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
        GROUP BY month(tblTransFeesPaid.Pay_Date)
    ) t2

  ) t3

  GROUP BY Month;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484577.html

標籤：mysql 加入交叉连接

上一篇：如何防止MySQL中的空格、空值和無效日期？

下一篇：使用特定條件排除記錄