sql where查询条件中有sum函数

第一个问题:分组了,就不用再用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

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

计算判断用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表中计算来的。

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