Question

mysql求sql语句 查询订单明细金额累加与订单总金额不等的订单

两张表 一张订单表order 里边有总金额subtotal
一张订单明细表order_details 里边按不同产品有金额cost
两张表通过order_id关联 现有很多订单
要求查询将订单明细金额累加后于订单总金额不等的订单找出来 返回所有不等的订单order_id

求高手解答

Answer 1

SELECT
o1.order_id,
MAX(o1.subtotal) AS 订单总金额,
SUM(o2.cost) AS 订单明细合计
FROM
`order` o1 JOIN order_details o2 ON (o1.order_id = o2.order_id)
GROUP BY
o1.order_id
HAVING
MAX(o1.subtotal) <> SUM(o2.cost)

Answer 2

select order_id from order as o left join order_details as d on o.order_id = d,order_id where o.subtotal = d.cost

Answer 3

select *
from `order` r inner join
(select order_id ,sum(cost) as c from order_details ) as x
on r.order_id = x.order_id
where r.subtotal <> x.c
或
select * from 'order' r where r.subtotal <> (select sum(cost) from order_details d where d.order_id =r.order_id)