我有3个表格：发票，人和付款。
我想列出一个发票清单，其中包含客户名称（来自人），付款总和和付款日期（来自付款）。
首先我做了这些陈述
SELECT V.id, V.datum, V.amount, P.name AS 'client',
(SELECT SUM(B.amount) FROM payement AS B WHERE B.invoiceId = V.id) AS 'payed',
(SELECT GROUP_CONCAT(B.datum SEPARATOR ',') FROM payement AS B WHERE B.invoiceId = V.id) AS 'date payement'
FROM invoice AS V
JOIN person AS P ON (V.clientId = P.id)
WHERE YEAR(V.datum) = '2015'
ORDER BY V.datum;
这可以提供我想要的东西（例如9月4日交易1000次，9月10日交易2400次），但是当我有很多发票时，工作非常缓慢。
+------+-----------+--------+--------+-------+---------------------+
| id | datum | amount | client | payed | date payement |
+------+-----------+--------+--------+-------+---------------------+
| 75 |2015-09-10 | 3400 |Sommers | 3400 |2015-09-04,2015-09-10|
+------+-----------+--------+--------+-------+---------------------+
所以我尝试了另一种说法。
SELECT V.id, V.datum, V.amount, P.name AS 'client', B.amount AS 'payed', B.datum 'date payement'
FROM invoice AS V
JOIN person AS P ON (V.clientId = P.id)
LEFT JOIN payement AS B ON B.invoiceId = V.id
WHERE YEAR(V.datum) = '2015'
ORDER BY V.datum;
但这给了我2张1张发票的行，当它用2笔交易支付时。
我可以用SQL解决它，还是在我的应用程序中（在Java中）解决它更好？
最佳答案
如果发票已分两次付款，您希望使用哪些详细信息？第一次还是第二次付款？
假设您想要总付款金额和最晚付款日期：-
SELECT V.id,
V.datum,
V.amount,
P.name AS 'client',
SUM(B.amount) AS 'payed',
MAX(B.datum) AS 'date payement'
FROM invoice AS V
JOIN person AS P ON (V.clientId = P.id)
LEFT OUTER JOIN payement AS B ON B.invoiceId = V.id
WHERE YEAR(V.datum) = '2015'
GROUP BY V.id,
V.datum,
V.amount,
P.name
ORDER BY V.datum
我不使用phpmyadmin。
mysql> EXPLAIN SELECT V.factuurnr,
-> V.datum,
-> V.somexcl,
-> P.naam AS 'client',
-> SUM(B.bedrag) AS 'payed',
-> GROUP_CONCAT(DATE_FORMAT(B.datum,'%d/%m/%y') SEPARATOR ',') AS 'date payement'
-> FROM verkoop AS V
-> JOIN persoon AS P ON (V.klantId = P.id)
-> LEFT JOIN betaling AS B ON B.docId = V.id
-> WHERE YEAR(V.datum) = '2015' and month(V.datum)=9
-> GROUP BY V.factuurnr,
-> V.datum,
-> V.somexcl,
-> P.naam
-> ORDER BY factuurnr;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+
| 1 | SIMPLE | V | ALL | NULL | NULL | NULL | NULL | 1576 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | P | eq_ref | PRIMARY | PRIMARY | 4 | meta.V.klantId | 1 | Using where |
| 1 | SIMPLE | B | ALL | NULL | NULL | NULL | NULL | 3291 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
关于mysql - mysql左联接一起采取行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33284896/