通過引用多個表的連接條件的子查詢刪除重複結果

使用下面的程式碼，我最初試圖不使用子查詢來消除“重複數據”（它在技術上不是重複數據，因為每個訂單都有幾行價格和重量不同的數據，但我想將我的結果合併到我只獲得不同的訂單號）但是沒有運氣只使用連接條件和 SUM/GROUP BY。我在第一次加入子查詢時採用了返回帶有“價格”和“已發貨”欄位的不同訂單。並且相應地工作，我收到了我需要的訂單。但是通過使用整個腳本，我意識到“carton_no”導致返回“重複”結果，所以我嘗試添加另一個子查詢，但我似乎收到了錯誤：

消息 107，級別 16，狀態 2，第 17 行列前綴“TCT”與查詢中使用的表名或別名不匹配。

提供錯誤的程式碼塊：

---

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 

這已經解決了，通過重組程序和學習可以刪除報告中不需要的一些列。我能夠擺脫第二個子查詢。謝謝，

引用自：https://dba.stackexchange.com/questions/173601