一段具有纪念意义的sql

客官°小女子只卖身不卖艺 2022-08-21 09:44 159阅读 0赞

select substr(paytime, 0, 10) as time,
           sum(nvl(nrm, 0)) as nrmcount,
           sum(nvl(jd, 0)) as jdcount,
           sum(nvl(xc, 0)) as xccount,
           sum(nvl(nr, 0)) as nrcount,
           sum(nvl(yh, 0)) as yhcount
      from (select distinct o.orderno,
                            o.paytime,
                            d1.goodscount    as nrm,
                            d2.goodscount    as jd,
                            d3.goodscount    as xc,
                            d4.goodscount    as nr,
                            d5.nrmgoodscount as yh
              from n_orderinfo o
              left join (select od1.*
                          from n_orderdetails od1
                         where od1.ordertype = '01') d1
                on d1.orderno = o.orderno
              left join (select od2.*
                          from n_orderdetails od2
                         where od2.ordertype = '02') d2
                on d2.orderno = o.orderno
            
              left join (select od3.*
                          from n_orderdetails od3
                         where od3.ordertype = '03') d3
                on d3.orderno = o.orderno
            
              left join (select od4.*
                          from n_orderdetails od4
                         where od4.ordertype = '04') d4
                on d4.orderno = o.orderno
            
              left join (select o1.*
                          from n_orderinfo o1, n_orderdetails od5
                         where od5.ordertype = '01'
                           and o1.orderstatus = '4'
                           and o1.shopid = '34467') d5
                on d5.orderno = o.orderno
            
             where o.orderno in (select o2.orderno
                                   from n_orderinfo o2
                                  where o2.orderstatus = '4'
                                    and o2.shopid = '34467'
                                    and o2.paytime >= '2016-04-22')) 
     group by substr(paytime, 0, 10) order by substr(paytime, 0, 10) desc