今天在检查报表接口的时候发现了一个非常大的bug,在同事的帮助下 解决了,为了长记性,我就在这里对这个问题做一个详细的描述剖析,以便以后可以快速解决同一类的问题。
接口需求:
如下图所示,在选取时间范围之后,我需要点击分别按照 天,月,年 来查看销售额(总销售额,退货金额,减免金额,实际收入)
我的理解误区:
我刚开始写这个接口的时候 想通过一些sql来获取数据库所有的数据(完成的订单和退货的订单) 然后在java程序里面分别过来退货订单,然后通过计算 总销售额-减免-退货 = 实际营业额收入,后来发现 按天 月 年 来查询的时候,
退货的金额过滤不出来,这就导致了问题:
SELECT DATE_FORMAT(o.create_tm,'%Y-%m') as `period`,
sum(o.price) as salesTotal,sum(o.price) as returnPrice,sum(o.prixx_redux) as reduction,o.mode
FROM
`ordex` o
WHERE (o.mode = 2 or o.mode= 1)
and o.pay = 1
and o.shop_id = 10000xx
and o.create_tm between '2015-12-12' and '2016-02-19'
group by MONTH(o.create_tm)
ORDER BY o.create_tm DESC
后来经过讨论,决定以下sql来解决这个问题:
SELECT r.period ,salesTotal,reduction ,ifnull (returnPrice,0) as returnPrice , (salesTotal- (reduction+ ifnull (returnPrice,0))) as allin from
(SELECT DATE_FORMAT(o.create_tm,'%Y-%m') as `period`,
sum(o.price) as salesTotal,sum(o.price_reduce) as reduction
FROM
`order` o
WHERE (o.mode = 2 or o.mode=1)
and o.pay = 1
and o.shop_id = 10000xx
and o.create_tm between '2015-01-12' and date_add('2016-02-19', INTERVAL 1 day)
group by MONTH(o.create_tm)
ORDER BY o.create_tm DESC ) r
LEFT JOIN
(SELECT DATE_FORMAT(o.create_tm,'%Y-%m') as `period`,
ifnull( sum(o.price),0) as returnPrice
FROM
`order` o
WHERE o.mode = 2
and o.pay = 1
and o.shop_id = 10000xx
and o.create_tm between '2015-01-12' and date_add('2016-02-19', INTERVAL 1 day)
group by MONTH(o.create_tm)
ORDER BY o.create_tm DESC
) r1
on r.period=r1.period
通过这样的连接 可以把mode=2的数据单独查出来 加入计算 已达计算的目的
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。