从第二个表中按金额摘要提取记录

Question

我的问题是：

SELECT fd.*
FROM `fin_document` as fd
LEFT JOIN `fin_income` as fi ON fd.id=fi.document_id
WHERE fd.dt_payment < NOW()
HAVING SUM(fi.amount) < fd.total_amount

这显然是不正确的，必须从fin_document检索所有记录，其中dt_payment早于NOW()。这部分没问题。但我必须通过这些文件上的付款来过滤它们。一份文件可以有一笔以上的付款( 2,3,4,5 .)。在fin_income中是这些付款。document_id表中有列fin_income，它是外键fin_income.document_id=fin_document.id。问题(至少对我而言)是，我没有一个特定的id标准，而且数量是从fin_income表的所有记录中生成的。我还必须找到仍然没有付款的记录(他们在fin_income中没有行)。

fin_document:
+-------------------+---------------------------+------+-----+---------+----------------+
| Field             | Type                      | Null | Key | Default | Extra          |
+-------------------+---------------------------+------+-----+---------+----------------+
| id                | int(11)                   | NO   | PRI | NULL    | auto_increment |
| dt_payment        | date                      | YES  |     | NULL    |                
| total_amount      | decimal(10,2)             | NO   |     | 0.00    |                       
+-------------------+---------------------------+------+-----+---------+----------------+

fin_income:
+------------------+---------------+------+-----+-------------------+----------------+
| Field            | Type          | Null | Key | Default           | Extra          |
+------------------+---------------+------+-----+-------------------+----------------+
| id               | int(11)       | NO   | PRI | NULL              | auto_increment |
| document_id      | int(11)       | YES  | MUL | NULL              |                |
| amount           | decimal(10,2) | YES  |     | 0.00              |                
+------------------+---------------+------+-----+-------------------+----------------+

Answer 1

我不确定我是否正确地理解了你，但你可以试试这个：

SELECT fd.*, SUM(IFNULL(fi.amount, 0)) as sum_amount, COUNT(fi.amount) as count_amount
FROM `fin_document` as fd
LEFT JOIN `fin_income` as fi ON fd.id=fi.document_id
WHERE fd.dt_payment < NOW()

GROUP BY fd.id

HAVING sum_amount < fd.total_amount # condition for searching by sum of payments

AND count_amount = {needed_count}   # condition for searching by count of payments;
                                    # documents without payments will have
                                    # sum and count equal to 0

所有聚合都在SELECT部件中进行，然后所有文档按id分组，以避免结果重复，并使使用聚合结果(SUM、COUNT)成为可能。最后，您可以申请所需的条件(关于日期，支付金额或付款的计数)。

注意:请注意，GROUP BY显著增加了大量数据的执行时间。

Answer 2

您可能只需要一个相关的子查询来测试收入。

drop table if exists fin_document,fin_income;
create table fin_document
(id                 int(11),                 
dt_payment         date    ,                          
total_amount       decimal(10,2)
)   ;        


create table fin_income
( id                int(11)       ,
 document_id       int(11)       ,
 amount            decimal(10,2) 
);

insert into fin_document values
(1,'2019-05-31',1000),
(2,'2019-06-10',1000),
(3,'2019-07-10',1000);

insert into fin_income values
(1,1,5),(1,1,5);

SELECT fd.*,(select coalesce(sum(fi.amount),0) from fin_income fi where fd.id=fi.document_id) income
FROM `fin_document` as fd
WHERE fd.dt_payment < NOW() and
fd.total_amount > (select coalesce(sum(fi.amount),0) from fin_income fi where fd.id=fi.document_id);

+------+------------+--------------+--------+
| id   | dt_payment | total_amount | income |
+------+------------+--------------+--------+
|    1 | 2019-05-31 |      1000.00 |  10.00 |
|    2 | 2019-06-10 |      1000.00 |   0.00 |
+------+------------+--------------+--------+
2 rows in set (0.00 sec)