我有三张桌子：
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`)
);
我插入一个协议，三个评估，五个付款行如下：
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)
所以，任何协议都有多重评估，都需要在不久的将来支付。它可能有多笔付款，这些付款可能与评估有关（即租金），也可能不是（即申请费）。
现在，在现实中，有多个协议，有多个评估和多个支付。
现在我想要的结果是覆盖两个表
Assessment和Payment中与Agreement相关联的所有行，按第一个一致性分组，第二个评估。此外，我需要为每个评估从PaymentsApplied表中汇总Payment金额，以便我们可以将其与Amount表中的Assessment进行比较。另外，如果任何付款与任何评估都没有关联，则不要进行汇总。结果如下：+-----+-------+-----------+-----------------+--------------------+-----------------+
| 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如何根据assessment id（asmid）聚合付款行并与
AmountDue表联接？更新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 ;