摘要：今天在檢查報(bào)表接口的時(shí)候發(fā)現(xiàn)了一個(gè)非常大的，在同事的幫助下解決了，為了長(zhǎng)記性，我就在這里對(duì)這個(gè)問題做一個(gè)詳細(xì)的描述剖析，以便以后可以快速解決同一類的問題。

今天在檢查報(bào)表接口的時(shí)候發(fā)現(xiàn)了一個(gè)非常大的bug，在同事的幫助下 解決了，為了長(zhǎng)記性，我就在這里對(duì)這個(gè)問題做一個(gè)詳細(xì)的描述剖析，以便以后可以快速解決同一類的問題。
接口需求:
如下圖所示，在選取時(shí)間范圍之后，我需要點(diǎn)擊分別按照 天，月，年 來(lái)查看銷售額(總銷售額，退貨金額，減免金額，實(shí)際收入)

我的理解誤區(qū):
我剛開始寫這個(gè)接口的時(shí)候 想通過一些sql來(lái)獲取數(shù)據(jù)庫(kù)所有的數(shù)據(jù)(完成的訂單和退貨的訂單) 然后在java程序里面分別過來(lái)退貨訂單，然后通過計(jì)算 總銷售額-減免-退貨 = 實(shí)際營(yíng)業(yè)額收入，后來(lái)發(fā)現(xiàn) 按天 月 年 來(lái)查詢的時(shí)候，
退貨的金額過濾不出來(lái)，這就導(dǎo)致了問題：

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
    

后來(lái)經(jīng)過討論，決定以下sql來(lái)解決這個(gè)問題:

   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的數(shù)據(jù)多帶帶查出來(lái) 加入計(jì)算 已達(dá)計(jì)算的目的