问引用多个表的联接条件，通过子查询删除重复的结果

EN

LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB ON  TRCB.order_no = TCT.tdc_carton_tx 

DECLARE @location VARCHAR(10)
DECLARE @dateshipped DATETIME 
SET @location = 'RAS 2'
SET @DateShipped = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())-3)


SELECT  

      SUM (L.Shipped ) AS Shipped
    , SUM (L.Price ) AS Price
    , O.Order_no
    , O.Ext
    , O.Cust_Code 
    , O.Ship_To_Name 
    , O.Freight_Allow_Type 
    , TCT.Carton_no 
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.Number_of_Boxes 
    , MAX(TRCB.Skid_Height_Inches )
    , MAX(O.Routing) AS 'Routing'  
    , MAX(O.User_def_fld9) AS 'bol_no'  
    , MAX(Ship_to_name) AS Ship_to_name_1 
    , MAX(Ship_to_add_3) AS Ship_to_add_3 
    , MAX(L.[location]) AS 'Location' 
    , MAX(O.cust_code ) AS 'Cust_code_1' 
    , MAX(O.user_def_fld10) AS 'Skids' 
    , MAX(O.user_def_fld12) AS 'Weight' 
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
    , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'




FROM orders O WITH (NOLOCK)
INNER JOIN  
    (
       SELECT  o.order_no, L.shipped, L.price FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no
        WHERE O.routing NOT LIKE 'FEDX%' 
        AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') 
        AND ISNULL(O.void,'') <> 'V'
        AND L.location = @location
        GROUP BY O.order_no, L.shipped, L.price
   )
   L ON O.order_no = L.order_no
JOIN xfers X
     ON O.order_no = X.xfer_no
JOIN xfer_list XL
    ON  X.xfer_no = XL.xfer_no
--INNER JOIN ord_list L WITH (NOLOCK)
--  ON  O.order_no = L.order_no
--     AND O.ext = L.order_ext
INNER JOIN arcust C WITH (NOLOCK)
    ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
    ON A.customer_code = O.cust_code  
       AND A.ship_to_code = O.ship_to
       AND A.location_code = O.location  
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
     ON TCT.carton_type=TPM.pkg_code
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
     ON TPM.pkg_code = TCT.carton_type
LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB
        ON  TRCB.order_no = TCT.tdc_carton_tx 
LEFT OUTER JOIN ras_bol_details BD WITH (NOLOCK)
    ON X.xfer_no = BD.bl_src_no
       AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
LEFT OUTER JOIN ras_bol_header BH WITH (NOLOCK)
    ON BD.bl_no = BH.bl_no
       AND O.order_no = BD.bl_src_no 
       AND O.ext = BD.bl_src_ext 

WHERE  DATEADD(day, 0, DATEDIFF(day, 0, O.date_shipped)) >= @dateshipped




GROUP BY

      O.order_no
    , O.ext
    , O.cust_code
    , O.ship_to_name 
    , O.freight_allow_type 
    , TCT.carton_no 
    , TCT.[weight]
    , TPM.[weight]
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.number_of_boxes 
    , TRCB.skid_height_inches