牛神级的sql语句

顺德公农庄

　　select row_.*, rownum rownum_
　　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