超强的sql语句

from (select partyId,

           partyName,

           partyCode,

           sum(orderNum) as orderNum,

           sum(orderAmount) as orderAmount

      from (select pp.id as partyId,

                   pp.name as partyName,

                   pp.code as partyCode,

                   count(tso.id) as orderNum,

                   "SUM"(case

                           when tsoh.order_type = 10 then

                            (TSO.UNIT_PRICE * TSO.FINAL_QUANTITY -

                            (tso.FINAL_QUANTITY / tso.ORDERING_QUANTITY) *

                            (TSO.COUPON + TSO.DISCOUNT_FEE))

                           ELSE

                            (-TSO.unit_price * tso.FINAL_QUANTITY +

                            (tso.FINAL_QUANTITY / tso.ORDERING_QUANTITY) *

                            (TSO.COUPON + TSO.DISCOUNT_FEE))

                         end) as orderAmount

              from TRA_SALES_ORDER tso

             INNER JOIN GOO_GOODS G

                ON G.ID = tso.GOODS_ID

             INNER JOIN TRA_SALES_ORDER_HEADER tsoh

                on TSOH.id = TSO.ORDER_HEADER_ID

              left join par_party pp

                on pp.id = tsoh.buyer_id

             INNER JOIN WH_WARE_HOUSE wwh

                ON wwh.id = tsoh.warehouse_id

             WHERE tso.ORDER_STATUS > 300

               and tso.ORDER_STATUS < 305

               and tso.final_quantity > 0

               and TSOH.Warehouse_Id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘warehouse‘)

               and G.brand_id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘brand‘)

               and G.product_id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘product‘)

               and pp.party_type = 40

               and wwh.id = 1403072229050000

               and tso.BRANCH_COMPANY_ID = 2

               AND tso.HAS_INVOICE = 0

             group by pp.id, pp.name, pp.code

            UNION ALL

            select pp.id as partyId,

                   pp.name as partyName,

                   pp.code as partyCode,

                   count(tso.id) as orderNum,

                   "SUM"(case

                           when tsoh.order_type = 10 then

                            (TSO.UNIT_PRICE * TSO.FINAL_QUANTITY -

                            (tso.FINAL_QUANTITY / tso.ORDERING_QUANTITY) *

                            (TSO.COUPON + TSO.DISCOUNT_FEE))

                           ELSE

                            (-TSO.unit_price * tso.FINAL_QUANTITY +

                            (tso.FINAL_QUANTITY / tso.ORDERING_QUANTITY) *

                            (TSO.COUPON + TSO.DISCOUNT_FEE))

                         end) as orderAmount

              from TRA_SALES_ORDER_OFFLINE tso

             INNER JOIN GOO_GOODS G

                ON G.ID = tso.GOODS_ID

             INNER JOIN TRA_SALES_ORDER_HEADER tsoh

                on TSOH.id = TSO.ORDER_HEADER_ID

              left join par_party pp

                on pp.id = tsoh.buyer_id

             INNER JOIN WH_WARE_HOUSE wwh

                ON wwh.id = tsoh.warehouse_id

             WHERE tso.ORDER_STATUS > 300

               and tso.ORDER_STATUS < 305

               and tso.final_quantity > 0

               and TSOH.Warehouse_Id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘warehouse‘)

               and G.brand_id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘brand‘)

               and G.product_id in

                   (select cd.domain_id

                      from com_domain cd

                     where cd.source_id = 1608171622470497

                       and cd.source_type = ‘department‘

                       and cd.domain_type = ‘product‘)

               and pp.party_type = 40

               and wwh.id = 1403072229050000

               and tso.BRANCH_COMPANY_ID = 2

               AND tso.HAS_INVOICE = 0

             group by pp.id, pp.name, pp.code) tso

     group by partyId, partyName, partyCode

     order by orderAmount ASC) row_

where rownum <= 45;

--1608171622470497(Long), 1608171622470497(Long), 1608171622470497(Long), 40(Integer), 1511161907500008(Long), 2(Long), false(Boolean), 1608171622470497(Long), 1608171622470497(Long), 1608171622470497(Long), 40(Integer), 1511161907500008(Long), 2(Long), false(Boolean)

超强的sql语句

标签：nal   dep   group   art   left join   bool   _id   sum   war