sql查找子列之和不等于父列的父表

Question

嘿，伙计们，我正试图从billMaster中找到一张账单，其中billDetails.total的总和不等于billMaster.remainingAmount

注意这是一对多关系，一张账单可以包含更多账单详细信息

我尝试了下面的查询，得到了一个sql错误

/* SQL Error (1111): Invalid use of group function */

SELECT a.id AS billMAsterId FROM eBillMaster AS a JOIN eBillDetail AS b ON a.id = b.billId
WHERE SUM(b.total) !=  b.remainAmount GROUP BY a.id 

SELECT a.remainAmount, a.id AS BillId FROM eBillMaster a JOIN (SELECT MAX(id) AS id FROM eBillMaster) b JOIN eBillDetail c ON (a.id - c.billId) WHERE SUM(c.total) != a.remainAmount

两个查询都返回了相同的错误，我认为这与我在where close上使用sum的方式有关。但可悲的是，我不能解决这个问题。。如有任何回应，我们将不胜感激。

现在假设我想获得满足上述条件的最近用户账单。请注意，billMaster有一个名为user_id的列。新查询会是什么样子。

Answer 1

一个例子会让思考变得更容易，但我认为这正是你想要的：

SELECT m.id
FROM eBillMaster AS m 
JOIN (select billId, sum(total) sumTotal from eBillDetail group by billId) AS d ON m.id = d.billId
WHERE d.sumTotal != m.remainAmount;

在这里查看http://sqlfiddle.com/#！9/89dcfb/7

Answer 2

使用cross apply(也称为lateral join(更有效-根据您的sql方言，您没有标记数据库

select a.id as billMAsterId 
from eBillMaster as a 
cross apply (
select Sum(total) total
from eBillDetail as b
where b.billId = a.id 
)b
where a.remainAmount != b.total

要对having子句执行同样的操作，您可以执行

select a.id as billMAsterId 
from eBillMaster as a 
join eBillDetail as b on a.id = b.billId
group by a.id, a.remainAmount
having Sum(b.total) != a.remainAmount

Answer 3

您需要使用hasing子句，还需要通过remainamount 进行分组

with ebillmaster (id) as(
select 1 from dual union all
select 2 from dual union all
select 3 from dual),
ebilldetail (billid, total, remainamount) as(
select 1, 4, 5 from dual union all
select 2, 3, 4 from dual union all
select 3, 3, 3 from dual union all
select 4, 1, 2 from dual)
SELECT
a.id AS billmasterid
FROM
ebillmaster   a
JOIN ebilldetail   b ON a.id = b.billid
GROUP BY
a.id,
b.remainamount
HAVING
SUM(b.total) != b.remainamount

Answer 4

这是标准SQL，聚合值在HAVING子句中进行筛选。

SELECT a.id AS billMAsterId 
FROM eBillMaster AS a 
JOIN eBillDetail AS b ON a.id = b.billId
GROUP BY a.id, a.remainAmount
HAVING SUM(b.total) != a.remainAmount

当eBillMaster.id为PK时，一些Sql引擎允许从GROUP BY中省略a.remainAmount。多一个(奇异的一点(选项

SELECT a.id AS billMAsterId 
FROM eBillMaster AS a 
JOIN eBillDetail AS b ON a.id = b.billId
GROUP BY a.id
HAVING SUM(b.total) != AVG(a.remainAmount)

Answer 5

感谢大家的反馈，它让我找到了正确的路径，修改后的查询是

SELECT m.propertyId, d.sumTotal, m.remainAmount, m.id
FROM eBillMaster AS m JOIN ( SELECT MAX(id) AS id FROM eBillMaster GROUP BY propertyId) b USING (id)
JOIN (select billId, sum(total) sumTotal from eBillDetail group by billId) AS d ON m.id = d.billId
WHERE d.sumTotal != m.remainAmount;