mysql中的sql-sum()

tgabmvqs 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(366)

下面的查询显示了一个合同的结果，其合同延迟为 between 1 and 487 days .
现在我要添加值属于 invoice.value 然后根据合同的余额进行过滤，其总和介于一个值和另一个值之间。
在这种情况下，我将有8个结果，将符合下面的查询，与合同的余额 between 4 and 5 dolars and delay between 1 and 487 days ，但我只得到一个结果，但是，不正确。
我做错什么了？
tb\U合同

id
contract

tb\ U发票

id
contract_id
value
due_date

查询

SELECT DISTINCT `contract`.*
FROM `tb_contract` `contract`
LEFT JOIN `tb_invoice` `invoice` ON `invoice`.`contract_id` = `contract`.`id`
WHERE `contract`.`creditor_id` = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'
AND DATEDIFF(CURDATE(), invoice.due_date) >= 1
AND DATEDIFF(CURDATE(), invoice.due_date) <= 487
GROUP BY `contract`
HAVING SUM(invoice.value) between 4.00 AND 5.00

不使用having使用where invoice.value ，我收到一个合同，其中至少有一个发票符合值的参数，但是它带来了同一个合同中包含其他几个值的其他发票在咨询的参数之上。我要添加的是合同的所有发票，并且只在参数值之间进行筛选。换句话说，合计所有合同发票不能低于或高于金额
那将是另一个问题

SELECT DISTINCT `contract`.*
FROM `tb_contract` `contract` LEFT JOIN `tb_invoice` `invoice` ON `invoice`.`contract_id` = `contract`.`id`
WHERE `contract`.`creditor_id` = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'
AND `invoice`.`value` between 4.00 AND 5.00
AND DATEDIFF(CURDATE(), invoice.due_date) >= 1
AND DATEDIFF(CURDATE(), invoice.due_date) <= 487

sql mysql Join Date having-clause

来源：https://stackoverflow.com/questions/62416548/sum-in-a-where-mysql

2条答案

按热度按时间

szqfcxe21#

我对这件事很怀疑 group by 条款：is contract 在查询中起作用的任何表中的实际列？要使查询成为有效的sql，它必须是contracts表的主键。
我还怀疑您想要的合同的所有发票都属于给定的日期间隔-因此该日期上的条件应该移到 having 条款。
总的来说，我认为通过将contract表与invoices上的聚合子查询连接起来，可以更简单、更有效地表达：

select c.*, i.total_value
from tb_contract contract c
inner join (
    select contract_id, sum(value) total_value
    from tb_invoice
    group by contract_id
    having 
        max(i.due_date) >= current_date - interval 487 day
        and min(i.due_date) < current_date
        and sum(value) >= 4 
        and sum(value) <  5
) i on i.contract_id = c.id
where c.creditor_id = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'

赞(0）回复(0）举报 2021-07-26

ldfqzlk82#

SELECT `contract`.*,SUM(invoice.value) AS inv_tot
FROM `tb_contract` `contract`
LEFT JOIN `tb_invoice` `invoice` ON `invoice`.`contract_id` = `contract`.`id`
WHERE `contract`.`creditor_id` = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'
AND DATEDIFF(CURDATE(), invoice.due_date) >= 1
AND DATEDIFF(CURDATE(), invoice.due_date) <= 487
GROUP BY `contract`
HAVING inv_tot between 4.00 AND 5.00

赞(0）回复(0）举报 2021-07-26

我来回答

mysql中的sql-sum()

2条答案

相关问题

热门标签

最新问答