Question

sql where查询条件中有sum函数

select distinct a.code as 订单编号,a.amount as 订单总金额,
sum(b.amount + b.amount_lans) as 明细总金额
from a
left join b on a.order_id = b.order_id
where a.total_amount - sum(b.amount + b.amount_lans) <> 0
group by a.code,a.amount

执行后报错 "aggregates not allowed in WHERE clause"
应该是sum函数不能出现在查询条件里

我要查询出订单总金额-明细总金额不为0的结果
请教高手sql应该怎么写
补充：用了having做后置条件，查询结果里有 订单总金额=明细总金额的情况。为什么?
我现在having 订单总金额-明细总金额 <> 0
查出的结果 订单总金额(比如：100) = 明细总金额(比如：100)

Answer 1

第一个问题:分组了,就不用再用DISTINCT了
第二个问题:WHERE后跟分组前条件,HAVING是分组后条件
第三个问题:你也用总金额为分组,不知道你有什么特定目的.
select a.code as 订单编号,a.amount as 订单总金额,
sum(b.amount + b.amount_lans) as 明细总金额
from a
left join b on a.order_id = b.order_id
group by a.code,a.amount
having a.total_amount - sum(b.amount + b.amount_lans) <> 0
估计这个效果才是你要的
select a.code as 订单编号,sum(a.amount) as 订单总金额,
sum(b.amount + b.amount_lans) as 明细总金额
from a
left join b on a.order_id = b.order_id
group by a.code
having a.total_amount - sum(b.amount + b.amount_lans) <> 0

Answer 2

sum不是这样用的：

select * from
(
select 订单编号,订单总金额,sum(明细金额) as 明细总金额 from
(select a.code as 订单编号,a.amount as 订单总金额,
b.amount + b.amount_lans as 明细金额 from a
left join b on a.order_id = b.order_id
)c
group by 订单编号,订单总金额
)d
where 订单总金额-明细总金额<>0

Answer 3

计算判断用having

select distinct a.code as 订单编号,a.amount as 订单总金额,
sum(b.amount + b.amount_lans) as 明细总金额
from a
left join b on a.order_id = b.order_id
group by a.code,a.amount
having a.total_amount - sum(b.amount + b.amount_lans) <> 0
订单总金额=明细总金额的情况不是很正常吗？明细总金额是从B表中计算来的。

Answer 4

select * from (
select distinct a.code as 订单编号,a.amount as 订单总金额,
sum(b.amount + b.amount_lans) as 明细总金额
from a
left join b on a.order_id = b.order_id) A
where total_amount - 明细总金额 <> 0
group by code,amount