我有三个表:

CREATE TABLE `Agreement` (
  `AID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FLAGS` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`AID`)
);

CREATE TABLE `Assessment` (
  `ASMID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AID` bigint(20) NOT NULL DEFAULT '0',
  `Amount` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Description` text,
  PRIMARY KEY (`ASMID`)
);

CREATE TABLE `Payment` (
  `RID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AID` bigint(20) NOT NULL DEFAULT '0',
  `ASMID` bigint(20) NOT NULL DEFAULT '0',
  `Amount` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Description` text,
  PRIMARY KEY (`RID`)
);

我要插入一个协议(protocol)、三个评估、五个付款行，如下所示:

INSERT INTO Agreement(FLAGS) VALUES(0);
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 1200, "Rent");
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 20, "Damage - car break");
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 500, "Damage - vehicle");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 1, 500, "Rent Fee");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 1, 600, "Rent Fee");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 2, 20, "Damage Fee");
INSERT INTO Payment(AID, Amount, Description) VALUES(1, 600, "Deposit Fee");
INSERT INTO Payment(AID, Amount, Description) VALUES(1, 50, "Application Fee");

当我看到数据时，它应该是这样的:

mysql> SELECT * FROM Agreement;
+-----+-------+
| AID | FLAGS |
+-----+-------+
|   1 |     0 |
+-----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Assessment;
+-------+-----+-----------+--------------------+
| ASMID | AID | Amount    | Description        |
+-------+-----+-----------+--------------------+
|     1 |   1 | 1200.0000 | Rent               |
|     2 |   1 |   20.0000 | Damage - car break |
|     3 |   1 |  500.0000 | Damage - vehicle   |
+-------+-----+-----------+--------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Payment;
+-----+-----+-------+----------+-----------------+
| RID | AID | ASMID | Amount   | Description     |
+-----+-----+-------+----------+-----------------+
|   1 |   1 |     1 | 500.0000 | Rent Fee        |
|   2 |   1 |     1 | 600.0000 | Rent Fee        |
|   3 |   1 |     2 |  20.0000 | Damage Fee      |
|   4 |   1 |     0 | 600.0000 | Deposit Fee     |
|   5 |   1 |     0 |  50.0000 | Application Fee |
+-----+-----+-------+----------+-----------------+
5 rows in set (0.00 sec)

因此，任何协议(protocol)都有多个评估都需要在不久的将来支付。它可能有多项付款，这些付款可能与评估相关(即租金)，也可能不相关(即申请费)。

现在，在现实中，有多个协议(protocol)具有多个评估和多个付款。

现在我想要涵盖两个表中所有行的结果 Assessment和 Payment与 Agreement 有关GROUPED BY 第一个协议(protocol)，第二个评估。此外，我需要将 AMOUNT 合计为 PaymentsApplied来自表 Payment每个评估的表格，以便我们可以将其与 Amount 进行比较来自表 Assessment作为AmountDue .另外，如果任何付款与任何评估都不相关，则不要进行汇总。结果将如下所示:

+-----+-------+-----------+-----------------+--------------------+-----------------+
| AID | ASMID | AmountDue | PaymentsApplied |          ASM-Descr | PMT-Description |
+-----+-------+-----------+-----------------+--------------------+-----------------+
|   1 |     1 | 1200.0000 |       1100.0000 |               Rent |        Rent Fee |
|   1 |     2 |   20.0000 |         20.0000 | Damage - car break |     Damange Fee |
|   1 |     3 |  500.0000 |            NULL |   Damage - vehicle |            NULL |
|   1 |     0 |      NULL |        600.0000 |               NULL |     Deposit Fee |
|   1 |     0 |      NULL |         50.0000 |               NULL | Application Fee |
+-----+-------+-----------+-----------------+--------------------+-----------------+
5 Rows

我尽力解释情况。实际上，在我的应用程序查询中已经连接了 10 多个表，例如 Agreement!

非常欢迎任何帮助!!

更新 1

我从这个查询开始，

(SELECT DISTINCT
    Payment.RID, Payment.Amount as PaymentsApplied, Payment.ASMID as PMT_ASMID, null as AmountDue, null AS ASMID
FROM Payment
    LEFT JOIN Assessment ON Assessment.ASMID=Payment.ASMID)
UNION
(SELECT DISTINCT
    null, null, null, Assessment.Amount, Assessment.ASMID
FROM Assessment
    LEFT JOIN Payment ON Payment.ASMID=Assessment.ASMID)
ORDER BY ASMID, PMT_ASMID;

这给了我结果，

+------+-----------------+-----------+-----------+-------+
| RID  | PaymentsApplied | PMT_ASMID | AmountDue | ASMID |
+------+-----------------+-----------+-----------+-------+
| NULL |            NULL |      NULL | 1200.0000 |     1 |
| NULL |            NULL |      NULL |   20.0000 |     2 |
| NULL |            NULL |      NULL |  500.0000 |     3 |
|    1 |        500.0000 |         1 |      NULL |  NULL |
|    2 |        600.0000 |         1 |      NULL |  NULL |
|    3 |         20.0000 |         2 |      NULL |  NULL |
|    4 |        600.0000 |         0 |      NULL |  NULL |
|    5 |         50.0000 |         0 |      NULL |  NULL |
+------+-----------------+-----------+-----------+-------+
8 rows in set (0.01 sec)

现在，从这一点开始，IDK 如何通过评估 ID (ASMID) 聚合付款行并加入 Agreement还有 table ？

更新 2

我做了 sqlfiddle link以防万一有人想尝试。

我在查询中添加了条件聚合，

(SELECT DISTINCT
    null as AmountDue,
    null AS ASMID,
    null as ASM_Descr,
    Payment.Description as PMT_Descr,
    (CASE WHEN Payment.ASMID > 0 THEN SUM(Payment.Amount) ELSE Payment.Amount END) as PaymentsApplied,
    (CASE WHEN Payment.ASMID > 0 THEN GROUP_CONCAT(Payment.RID) ELSE Payment.RID END) as PaymentList,
    Payment.ASMID as PMT_ASMID
FROM Payment
    LEFT JOIN Assessment ON Assessment.ASMID=Payment.ASMID
    GROUP BY Assessment.ASMID)
UNION ALL
(SELECT DISTINCT
    Assessment.Amount,
    Assessment.ASMID,
    Assessment.Description,
    null,
    null,
    null,
    null
FROM Assessment
    LEFT JOIN Payment ON Payment.ASMID=Assessment.ASMID
    GROUP BY Assessment.ASMID)
ORDER BY ASMID, PMT_ASMID;

这给了我，

+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+
| AmountDue | ASMID | ASM_Descr          | PMT_Descr   | PaymentsApplied | PaymentList | PMT_ASMID |
+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+
|      NULL |  NULL | NULL               | Deposit Fee |        600.0000 | 4           |         0 |
|      NULL |  NULL | NULL               | Rent Fee    |       1100.0000 | 1,2         |         1 |
|      NULL |  NULL | NULL               | Damage Fee  |         20.0000 | 3           |         2 |
| 1200.0000 |     1 | Rent               | NULL        |            NULL | NULL        |      NULL |
|   20.0000 |     2 | Damage - car break | NULL        |            NULL | NULL        |      NULL |
|  500.0000 |     3 | Damage - vehicle   | NULL        |            NULL | NULL        |      NULL |
+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+

但是，付款行 (RID: 5) 中仍然缺少一行，我没有得到预期的结果。

最佳答案

我会先收集所有的评估，将它们加入付款，然后合并所有没有评估的付款:

# Assessments with payments

SELECT asm.AID,
       asm.ASMID,
       min(asm.Amount) AS AmountDue,
       SUM(pam.Amount) AS PaymentsApplied,
       asm.Description AS `ASM-Descr`,
       pam.Description AS `PMT-Descr`,
       agr.FLAGS
FROM Assessment asm
LEFT JOIN Payment pam ON pam.ASMID = asm.ASMID
JOIN Agreement agr ON agr.AID = asm.AID
GROUP BY asm.AID,
         asm.ASMID
UNION # Payments without assessments

SELECT pam.AID,
       pam.ASMID,
       NULL AS AmountDue,
       SUM(pam.Amount) AS PaymentsApplied,
       NULL AS `ASM-Descr`,
       pam.Description AS `PMT-Descr`,
       agr.FLAGS
FROM Payment pam
LEFT JOIN Assessment asm ON pam.ASMID = asm.ASMID
JOIN Agreement agr ON agr.AID = pam.AID
WHERE asm.ASMID IS NULL
GROUP BY pam.AID, pam.RID;

如果你想添加更多信息，你可以包装这个结果，给它一个名字并将更多的表加入到临时结果中:

SELECT payment_overview.*,
       p.name
FROM
    ( # Assessments with payments
 SELECT asm.AID,
        asm.ASMID,
        min(asm.Amount) AS AmountDue,
        SUM(pam.Amount) AS PaymentsApplied,
        asm.Description AS `ASM-Descr`,
        pam.Description AS `PMT-Descr`,
        agr.FLAGS
     FROM Assessment asm
     LEFT JOIN Payment pam ON pam.ASMID = asm.ASMID
     JOIN Agreement agr ON agr.AID = asm.AID
     GROUP BY asm.AID,
              asm.ASMID
     UNION # Payments without assessments
 SELECT pam.AID,
        pam.ASMID,
        NULL AS AmountDue,
        SUM(pam.Amount) AS PaymentsApplied,
        NULL AS `ASM-Descr`,
        pam.Description AS `PMT-Descr`,
        agr.FLAGS
     FROM Payment pam
     LEFT JOIN Assessment asm ON pam.ASMID = asm.ASMID
     JOIN Agreement agr ON agr.AID = pam.AID
     WHERE asm.ASMID IS NULL
     GROUP BY pam.AID,
              pam.RID ) AS payment_overview
JOIN Payor p ON p.AID = payment_overview.AID ;

关于具有条件聚合的 MySQL UNION ALL (Full Join)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46805061/