MySQL：使用Join和Union从单行中获取多个表的数据

Question

我有三个数据库表“客户”，“帐单”和“交易”

客户：

cuid	名称	地址
1	David	City 1 2
Roja City 2	Roja	City 2

帐单：< /strong>

id	cuid	月	bill_amount
1	1	1 1	100
2	1	2	200
3	2	1	400
4	1	3	100

交易：

id	cuid	date	recceede_amount
1	1	2022-3-3-02	250
2	---	2022-2-2-2-2 02	200
3	2	2022-3-02	200

我需要一个新生成的应有桌子，在计算月份的适当金额后使用这样的fifo：

cuid	name name	dame	dodair	due_amount
1	David	City 1	2	50
1	David City 1 David	City 1 David City 1	3	100

此代码无法正常工作。 MySQL代码：

SELECT 
    due.cuid,
    due.Name,
    due.Address,
    due.Month,
    sum(due.Amount - due.Received) AS Due_Amount
FROM 
    (SELECT 
        c.cuid,
        c.name AS name,
        c.address AS address,
        b.month AS Month,
        0 AS Received,
        b.bill_amount AS Amount
    FROM 
        customer c
        INNER JOIN billing b ON c.cuid=b.cuid
    
    UNION ALL
    SELECT 
        c.cuid,
        c.name AS Name,
        c.address AS Address,
        null AS Month,
        t.received_amount AS Received,
        0 AS Amount
    FROM 
        customer c
        INNER JOIN transaction t ON c.cuid = t.cuid) AS due
GROUP BY 
    due.cuid;

生成的表格是：

cuid	姓名	地址	月	day_amount
1	David	City 1	1	150
2	Roja	City 2	1	0

Answer 1

您可能不需要所有这些sum（），联合所有和子查询。您可以在单个查询中进行操作，加入这样的所有三个表：

SELECT c.cuid,
       c.Name,
       c.Address,
       b.Month,
       t.received_amount-b.bill_amount AS Due_Amount
FROM customer c 
  JOIN billing b 
    ON c.cuid=b.cuid
  JOIN transaction t 
    ON c.cuid=t.cuid
    AND b.month=MONTH(t.date);

演示小提琴

我确实有一些好奇心，因为我对due_amount的理解是客户需要支付剩余的金额，而您的您似乎不是一个。如果是这样，则该值应该为负，因为客户David比bill_amount付费更多（receed_amount）。