Tweak SQL query help_问答_开发者_运维开发者技术经验分享

I have a big query as following, someone please tweak this query? i need to avoid this big group by ... :-)

SELECT  COALESCE( SUM(total_retail), 0 ) total_retail,
        COALESCE( SUM(meterial_sub_total), 0 ) meterial_sub_total,
        COALESCE( MIN(po_template_group_by_code), 0 ) po_template_group_by_code,
        COALESCE( MIN(po_template_sort_by_code), 0 ) po_template_sort_by_code
FROM
(
SELECT  
    COALESCE(  CASE WHEN COALESCE(ihbol.valuation_method_code, pih.valuation_method_code, @default_valuation_method_code, 'i') IN ('r', 'i', 'l') THEN
                      COALESCE ( rmiretail.retail_price / rmiretail.unit_of_measure_quantity
                                  / COALESCE( uomcvrmi.atomic_conversion_factor * UOMrmi.factor, UOMrmi.factor ), 0 )
                                  * poi.confirmed_quantity
                                  * ( CASE WHEN poi.catch_weight_flag = 'y'
                                           THEN ( poi.package_weight * COALESCE( uomcvpr.atomic_conversion_factor * UOMpr.factor, UOMpr.factor ) )
                                           WHEN si.shipper_flag = 'y'
                                           THEN ( meterial_data.linked_meterial_quantity * COALESCE( uomcvitm.atomic_conversion_factor * uomitm.factor, uomitm.factor ) )
                                           ELSE ( COALESCE( uomcvpkg.atomic_conversion_factor * UOMpkg.factor, UOMpkg.factor ) )
                                      END ) 
                    ELSE
                      NULL
                    END, 0 ) AS total_retail,
    CASE WHEN MIN(s.rcv_using_po_default_cost_target_code) = 'p' AND MIN(po.status_code) <> 't'
         THEN COALESCE( MIN(po.last_draft_total), 0 ) 
         ELSE  CASE WHEN meterial_data.linked_meterial_quantity IS NULL
                         THEN CASE WHEN poi.catch_weight_flag = 'y' 
                                   THEN COALESCE( poi.confirmed_quantity * poi.package_weight * spicost.net_supplier_price, 0 ) 
                                   ELSE COALESCE( poi.confirmed_quantity * spicost.net_supplier_price, 0 ) 
                              END
                         ELSE 0
                     END
    END AS meterial_sub_total
    ,po.po_template_group_by_code AS po_template_group_by_code
    ,po.po_template_sort_by_code AS po_template_sort_by_code

FROM  purchase_order po

JOIN  supplier s WITH (NOLOCK) 
ON    po.supplier_id = s.supplier_id

JOIN  purchase_order_meterial poi WITH (NOLOCK)
ON    po.purchase_order_id = poi.purchase_order_id
AND   po.business_unit_id  = poi.business_unit_id

JOIN  supplier_meterial si WITH (NOLOCK)
ON    poi.supplier_meterial_id = si.supplier_meterial_id
AND   poi.supplier_id      = si.supplier_id

JOIN  supplier_packaged_meterial  spi WITH (NOLOCK)
ON    spi.supplier_meterial_id = si.supplier_meterial_id 
AND   spi.supplier_id      = @supplier_id:int

JOIN  unit_of_measure  uompkg WITH (NOLOCK)
ON    uompkg.unit_of_measure_id = spi.packaged_in_uom_id 

JOIN  merch_bu_spi_cost_list  spicost  WITH (NOLOCK)
ON    spicost.business_unit_id = @bu_id:int
AND   spicost.supplier_id      = si.supplier_id 
AND   spicost.supplier_meterial_id = si.supplier_meterial_id 
AND   spicost.packaged_meterial_id = spi.packaged_meterial_id 

LEFT OUTER JOIN
  (
    SELECT  poi.supplier_meterial_id AS supplier_meterial_id,
            si.meterial_id           AS linked_meterial_id,
            NULL                 AS linked_meterial_uom,
            NULL                 AS linked_meterial_quantity

    FROM    purchase_order_meterial poi WITH (NOLOCK)

    JOIN    supplier_meterial si WITH (NOLOCK)
    ON      poi.supplier_meterial_id = si.supplier_meterial_id

    WHERE   poi.purchase_order_id = @po_id:int
    AND     poi.business_unit_id  = @bu_id:int

    UNION ALL

    SELECT  siil.supplier_meterial_id        AS supplier_meterial_id,
            siil.inventory_meterial_id       AS linked_meterial_id,
            siil.unit_of_measure_id      AS linked_meterial_uom,
            siil.inventory_meterial_quantity AS linked_meterial_quantity

    FROM    purchase_order_meterial poi WITH (NOLOCK)

    JOIN    supplier_inventory_meterial_list  siil WITH (NOLOCK)
    ON      siil.supplier_id = @supplier_id:int
    AND     poi.supplier_meterial_id = siil.supplier_meterial_id

    WHERE   poi.purchase_order_id = @po_id:int
    AND     poi.business_unit_id = @bu_id:int

  ) meterial_data
ON  meterial_data.supplier_meterial_id             = si.supplier_meterial_id

LEFT OUTER JOIN meterial                        i
ON    i.meterial_id                             = meterial_data.linked_meterial_id

LEFT OUTER JOIN meterial_hierarchy              ih
ON    ih.meterial_hierarchy_id                  = COALESCE(si.meterial_hierarchy_id, i.meterial_hierarchy_id)

LEFT OUTER JOIN meterial_hierarchy              pih
ON    i开发者_运维技巧h.setstring                          LIKE pih.setstring + '%'
AND   pih.meterial_hierarchy_level_id           = @meterial_hierarchy_level_id

LEFT OUTER JOIN meterial_hierarchy_bu_override_list ihbol
ON    ihbol.business_unit_id                = @bu_id:int
AND   ihbol.meterial_hierarchy_id               = pih.meterial_hierarchy_id

LEFT OUTER JOIN  retail_modified_meterial     rmi WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = rmi.retail_meterial_id
AND   rmi.retail_valuation_flag             = 'y'

LEFT OUTER JOIN merch_bu_rmi_retail_list  rmiretail WITH (NOLOCK)
ON    rmiretail.retail_modified_meterial_id     = rmi.retail_modified_meterial_id
AND   rmiretail.business_unit_id            = @bu_id:int

LEFT OUTER JOIN  unit_of_measure        uompr WITH (NOLOCK)
ON    uompr.unit_of_measure_id              = spi.priced_in_uom_id 

LEFT OUTER JOIN  unit_of_measure        uomitm WITH (NOLOCK)
ON    uomitm.unit_of_measure_id             = meterial_data.linked_meterial_uom

LEFT OUTER JOIN  unit_of_measure        uomrmi WITH (NOLOCK)
ON    uomrmi.unit_of_measure_id             = rmi.unit_of_measure_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvitm WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = uomcvitm.meterial_id
AND   uomitm.unit_of_measure_class_id       = uomcvitm.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvrmi WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = uomcvrmi.meterial_id
AND   uomrmi.unit_of_measure_class_id       = uomcvrmi.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvpr WITH (NOLOCK)
ON    uomcvpr.meterial_id                       = meterial_data.linked_meterial_id 
AND   uomcvpr.unit_of_measure_class_id      = uompr.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvpkg WITH (NOLOCK)
ON    uomcvpkg.meterial_id                      = meterial_data.linked_meterial_id 
AND   uomcvpkg.unit_of_measure_class_id     = uompkg.unit_of_measure_class_id

WHERE po.purchase_order_id = @po_id:int
AND   po.business_unit_id = @bu_id:int


GROUP BY poi.supplier_meterial_id, poi.confirmed_quantity, spicost.net_supplier_price, 
         meterial_data.linked_meterial_quantity,poi.catch_weight_flag,poi.package_weight, 
         ihbol.valuation_method_code, pih.valuation_method_code,rmiretail.retail_price,
         rmiretail.unit_of_measure_quantity, uomcvrmi.atomic_conversion_factor, UOMrmi.factor,
         UOMrmi.factor, uomcvpr.atomic_conversion_factor, UOMpr.factor, si.shipper_flag, 
         meterial_data.linked_meterial_quantity, uomcvitm.atomic_conversion_factor ,uomitm.factor, 
         uomitm.factor, uomcvpkg.atomic_conversion_factor,UOMpkg.factor, UOMpkg.factor,
         po.po_template_group_by_code, po.po_template_sort_by_code

) resultTable

Try:

SELECT  COALESCE( SUM(total_retail), 0 ) total_retail,
        COALESCE( SUM(meterial_sub_total), 0 ) meterial_sub_total,
        COALESCE( MIN(po_template_group_by_code), 0 ) po_template_group_by_code,
        COALESCE( MIN(po_template_sort_by_code), 0 ) po_template_sort_by_code
FROM
(
SELECT  
    sum(COALESCE(  CASE WHEN COALESCE(ihbol.valuation_method_code, pih.valuation_method_code, @default_valuation_method_code, 'i') IN ('r', 'i', 'l') THEN
                      COALESCE ( rmiretail.retail_price / rmiretail.unit_of_measure_quantity
                                  / COALESCE( uomcvrmi.atomic_conversion_factor * UOMrmi.factor, UOMrmi.factor ), 0 )
                                  * poi.confirmed_quantity
                                  * ( CASE WHEN poi.catch_weight_flag = 'y'
                                           THEN ( poi.package_weight * COALESCE( uomcvpr.atomic_conversion_factor * UOMpr.factor, UOMpr.factor ) )
                                           WHEN si.shipper_flag = 'y'
                                           THEN ( meterial_data.linked_meterial_quantity * COALESCE( uomcvitm.atomic_conversion_factor * uomitm.factor, uomitm.factor ) )
                                           ELSE ( COALESCE( uomcvpkg.atomic_conversion_factor * UOMpkg.factor, UOMpkg.factor ) )
                                      END ) 
                    ELSE
                      NULL
                    END, 0 )) AS total_retail,
    sum(CASE WHEN MIN(s.rcv_using_po_default_cost_target_code) = 'p' AND MIN(po.status_code) <> 't'
         THEN COALESCE( MIN(po.last_draft_total), 0 ) 
         ELSE  CASE WHEN meterial_data.linked_meterial_quantity IS NULL
                         THEN CASE WHEN poi.catch_weight_flag = 'y' 
                                   THEN COALESCE( poi.confirmed_quantity * poi.package_weight * spicost.net_supplier_price, 0 ) 
                                   ELSE COALESCE( poi.confirmed_quantity * spicost.net_supplier_price, 0 ) 
                              END
                         ELSE 0
                     END
    END) AS meterial_sub_total
    ,po.po_template_group_by_code AS po_template_group_by_code
    ,po.po_template_sort_by_code AS po_template_sort_by_code

FROM  purchase_order po

JOIN  supplier s WITH (NOLOCK) 
ON    po.supplier_id = s.supplier_id

JOIN  purchase_order_meterial poi WITH (NOLOCK)
ON    po.purchase_order_id = poi.purchase_order_id
AND   po.business_unit_id  = poi.business_unit_id

JOIN  supplier_meterial si WITH (NOLOCK)
ON    poi.supplier_meterial_id = si.supplier_meterial_id
AND   poi.supplier_id      = si.supplier_id

JOIN  supplier_packaged_meterial  spi WITH (NOLOCK)
ON    spi.supplier_meterial_id = si.supplier_meterial_id 
AND   spi.supplier_id      = @supplier_id:int

JOIN  unit_of_measure  uompkg WITH (NOLOCK)
ON    uompkg.unit_of_measure_id = spi.packaged_in_uom_id 

JOIN  merch_bu_spi_cost_list  spicost  WITH (NOLOCK)
ON    spicost.business_unit_id = @bu_id:int
AND   spicost.supplier_id      = si.supplier_id 
AND   spicost.supplier_meterial_id = si.supplier_meterial_id 
AND   spicost.packaged_meterial_id = spi.packaged_meterial_id 

LEFT OUTER JOIN
  (
    SELECT  poi.supplier_meterial_id AS supplier_meterial_id,
            si.meterial_id           AS linked_meterial_id,
            NULL                 AS linked_meterial_uom,
            NULL                 AS linked_meterial_quantity

    FROM    purchase_order_meterial poi WITH (NOLOCK)

    JOIN    supplier_meterial si WITH (NOLOCK)
    ON      poi.supplier_meterial_id = si.supplier_meterial_id

    WHERE   poi.purchase_order_id = @po_id:int
    AND     poi.business_unit_id  = @bu_id:int

    UNION ALL

    SELECT  siil.supplier_meterial_id        AS supplier_meterial_id,
            siil.inventory_meterial_id       AS linked_meterial_id,
            siil.unit_of_measure_id      AS linked_meterial_uom,
            siil.inventory_meterial_quantity AS linked_meterial_quantity

    FROM    purchase_order_meterial poi WITH (NOLOCK)

    JOIN    supplier_inventory_meterial_list  siil WITH (NOLOCK)
    ON      siil.supplier_id = @supplier_id:int
    AND     poi.supplier_meterial_id = siil.supplier_meterial_id

    WHERE   poi.purchase_order_id = @po_id:int
    AND     poi.business_unit_id = @bu_id:int

  ) meterial_data
ON  meterial_data.supplier_meterial_id             = si.supplier_meterial_id

LEFT OUTER JOIN meterial                        i
ON    i.meterial_id                             = meterial_data.linked_meterial_id

LEFT OUTER JOIN meterial_hierarchy              ih
ON    ih.meterial_hierarchy_id                  = COALESCE(si.meterial_hierarchy_id, i.meterial_hierarchy_id)

LEFT OUTER JOIN meterial_hierarchy              pih
ON    ih.setstring                          LIKE pih.setstring + '%'
AND   pih.meterial_hierarchy_level_id           = @meterial_hierarchy_level_id

LEFT OUTER JOIN meterial_hierarchy_bu_override_list ihbol
ON    ihbol.business_unit_id                = @bu_id:int
AND   ihbol.meterial_hierarchy_id               = pih.meterial_hierarchy_id

LEFT OUTER JOIN  retail_modified_meterial     rmi WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = rmi.retail_meterial_id
AND   rmi.retail_valuation_flag             = 'y'

LEFT OUTER JOIN merch_bu_rmi_retail_list  rmiretail WITH (NOLOCK)
ON    rmiretail.retail_modified_meterial_id     = rmi.retail_modified_meterial_id
AND   rmiretail.business_unit_id            = @bu_id:int

LEFT OUTER JOIN  unit_of_measure        uompr WITH (NOLOCK)
ON    uompr.unit_of_measure_id              = spi.priced_in_uom_id 

LEFT OUTER JOIN  unit_of_measure        uomitm WITH (NOLOCK)
ON    uomitm.unit_of_measure_id             = meterial_data.linked_meterial_uom

LEFT OUTER JOIN  unit_of_measure        uomrmi WITH (NOLOCK)
ON    uomrmi.unit_of_measure_id             = rmi.unit_of_measure_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvitm WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = uomcvitm.meterial_id
AND   uomitm.unit_of_measure_class_id       = uomcvitm.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvrmi WITH (NOLOCK)
ON    meterial_data.linked_meterial_id              = uomcvrmi.meterial_id
AND   uomrmi.unit_of_measure_class_id       = uomcvrmi.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvpr WITH (NOLOCK)
ON    uomcvpr.meterial_id                       = meterial_data.linked_meterial_id 
AND   uomcvpr.unit_of_measure_class_id      = uompr.unit_of_measure_class_id

LEFT OUTER JOIN  meterial_uom_conversion    uomcvpkg WITH (NOLOCK)
ON    uomcvpkg.meterial_id                      = meterial_data.linked_meterial_id 
AND   uomcvpkg.unit_of_measure_class_id     = uompkg.unit_of_measure_class_id

WHERE po.purchase_order_id = @po_id:int
AND   po.business_unit_id = @bu_id:int


GROUP BY po.po_template_group_by_code, po.po_template_sort_by_code

) resultTable

I think it should return the same results as the query above, although whether those results are what you actually want is a different matter...