T-Sql

將共享公共列的多個查詢結果集組合到一個指定的臨時表中

  • May 12, 2017

目前，我正在嘗試將三個視圖組合到一個儲存過程中以生成報告。起初，我開始對公共數據欄位使用連接，但發現其中一個表 ,XFER包含導致重複數據的數據。所以我認為最好的做法是獲取所有三個視圖並僅將我需要的數據插入到一個臨時表中。這樣做的原因是每個視圖都引用一個公共列order_no，並且在一個結果集中的所有三個視圖我想按順序明確定義一個結果集，並且在使用連接時沒有重複值。

我的問題：是否可以使用所有三個結果集中的選定列定義一個臨時表，並將它們合併到一個表中，儘管它們共享order_no？我嘗試了兩種方法：

  1. 首先創建一個臨時表，其中包含我希望從所有三個查詢集中呈現的所有列，然後從每個查詢中插入 INSERT INTO 以獲得組合結果
  2. 不要直接定義一個臨時表，並讓第一個 SELECT INTO 臨時表，然後剩下的兩個查詢使用 INSERT INTO

到目前為止，在嘗試了這兩種方法後，我收到了消息：

插入錯誤：列名或提供的值數與表定義不匹配。

   IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
   BEGIN
       DROP TABLE #TruckSummary
   END

   CREATE TABLE #TruckSummary
   (
       order_no int,
       freight_allow_type varchar(10),
       shipped decimal(20, 8),
       carton_no int,
       number_of_boxes int,
       skid_height_inches int,
       dim_ext_x float,
       dim_ext_y float,
       product_weight float,
       pallet_weight float,
       total_pallet_weight float,
       cubic_feet decimal(10, 4),
       PCF decimal(10, 4),
       routing varchar(20),
        bol_no int,
        ext int,
        ship_to_name varchar(40),
        ship_to_add_3 varchar(40),
        [location] varchar(10),
        cust_code varchar(10),
        date_shipped varchar(20),
        skids int,
        [weight] float,
        BOL_est_freight decimal(10, 2) 
   )




INSERT   INTO #TruckSummary


SELECT 
     O.order_no
   , O.freight_allow_type
   , L.shipped

FROM orders O (NOLOCK)
INNER JOIN ord_list L (NOLOCK)
   ON O.order_no = L.order_no 
      AND O.ext = L.order_ext AND ISNULL(L.void,'') <> 'Y'
INNER JOIN arcust C (NOLOCK)
   ON O.cust_code = C.customer_code
LEFT OUTER JOIN armaster A (NOLOCK)
   ON A.customer_code = O.cust_code
      AND A.ship_to_code = O.ship_to
WHERE O.status < 'S'
      AND ISNULL(O.void,'') <> 'V'
      AND O.date_shipped IS NULL

GROUP BY O.order_no, O.freight_allow_type, L.shipped 




INSERT INTO #TruckSummary

SELECT 
  TCT.order_no,
  TCT.carton_no, 
  TRCB.number_of_boxes, 
  TRCB.skid_height_inches, 
  TPM.dim_ext_x, 
  TPM.dim_ext_y, 
  TCT.weight AS 'product_weight',
  TPM.weight AS 'pallet_weight', 
  TCT.weight + TPM.weight AS 'total_pallet_weight',

  CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
       THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)
  ELSE 0 END AS 'cubic_feet',

  CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
    ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
       THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
  ELSE 0 END AS 'PCF',
  TCT.last_modified_date,
  TCT.modified_by,
  TCT.order_type
FROM   pltjones.dbo.tdc_carton_tx TCT (NOLOCK)
LEFT OUTER JOIN pltjones.dbo.tdc_revshelf_carton_box TRCB 
  ON TCT.order_no=TRCB.order_no 
     AND TCT.order_ext=TRCB.order_ext 
     AND TCT.carton_no=TRCB.carton_no 
LEFT OUTER JOIN pltjones.dbo.tdc_pkg_master TPM 
  ON TCT.carton_type=TPM.pkg_code
WHERE ISNULL(TCT.date_shipped,GETDATE()) > DATEADD(mm, -6, getdate())




INSERT INTO #TruckSummary

SELECT 
    MAX(O.routing) AS 'routing'
   , MAX(O.user_def_fld9) AS 'bol_no'
   , O.order_no 
   , O.ext 
   , MAX(ship_to_name) AS ship_to_name
   , MAX(ship_to_add_3) AS ship_to_add_3
   , MAX(OL.location) AS 'location'
   , MAX(O.cust_code ) AS 'cust_code'
   , CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
   , 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'
FROM orders O (NOLOCK)
    INNER JOIN ord_list OL (NOLOCK)
       ON O.order_no = OL.order_no 
            AND O.ext = OL.order_ext
   INNER JOIN ras_bol_details BD (NOLOCK)
       ON O.order_no = BD.bl_src_no 
            AND O.ext = BD.bl_src_ext 
            AND BD.order_type = 'S'
   LEFT OUTER JOIN ras_bol_header BH (NOLOCK)
       ON BH.bl_no = BD.bl_no
WHERE O.date_shipped >= dateadd(dd, -30, getdate())
            AND O.routing NOT LIKE 'FEDX%'
            AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
GROUP BY O.order_no, O.ext

UNION

SELECT TOP 100 PERCENT
   BH.routing
   , BD.bl_no
   , X.xfer_no
   , BD.bl_src_ext AS ext
   , X.to_loc_name
   , X.to_loc_addr3
   , X.from_loc
   , 'cust_no'
   , CONVERT(varchar(20) , X.date_shipped, 23)
   , BD.skids
   , BD.tare_wt
   , BH.est_freight_cost
FROM xfers X (NOLOCK)
JOIN xfer_list XL (NOLOCK)
   ON X.xfer_no = XL.xfer_no
JOIN ras_bol_details BD (NOLOCK)
   ON X.xfer_no = BD.bl_src_no 
      AND BD.order_type = 'T'
JOIN ras_bol_header BH (NOLOCK)
   ON BD.bl_no = BH.bl_no
WHERE X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
 AND X.date_shipped >  dateadd(dd, -30, getdate())

你的問題是你的臨時表#TruckSummary比你的選擇語句有更多的列。在這種情況下，您將需要提及列名（在您的 select 語句中）。

以下程式碼將給出您得到的確切錯誤。

CREATE TABLE [Sales].[Orders](
   [OrderID] [int] NOT NULL,
   [CustomerID] [int] NOT NULL,
   [SalespersonPersonID] [int] NOT NULL,
   [PickedByPersonID] [int] NULL
)

IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
       BEGIN
           DROP TABLE #TruckSummary
       END

       CREATE TABLE #TruckSummary
       (

       [OrderID] [int] NOT NULL,
       [CustomerID] [int] NOT NULL,
       [SalespersonPersonID] [int] NOT NULL,
       [PickedByPersonID] [INT]
       )

   INSERT   INTO #TruckSummary
   SELECT 
         [OrderID] ,
       [CustomerID], 
       [SalespersonPersonID]
   FROM sales.orders O (NOLOCK)
   Where orderid =1

   INSERT INTO #TruckSummary
   SELECT 
         [OrderID] ,
       [CustomerID], 
       [SalespersonPersonID]
   FROM sales.orders O (NOLOCK)
   WHERE orderid =2

以下程式碼將修復錯誤：

IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
   BEGIN
       DROP TABLE #TruckSummary
   END

   CREATE TABLE #TruckSummary
   (

   [OrderID] [int] NOT NULL,
   [CustomerID] [int] NOT NULL,
   [SalespersonPersonID] [int] NOT NULL,
   [PickedByPersonID] [INT]
   )

INSERT   INTO #TruckSummary
( [OrderID] ,
 [CustomerID], 
 [SalespersonPersonID]
   )
(
SELECT 
   [OrderID] ,
   [CustomerID], 
   [SalespersonPersonID]
FROM sales.orders O (NOLOCK)
Where orderid =1
)
INSERT   INTO #TruckSummary
( [OrderID] ,
 [CustomerID], 
 [SalespersonPersonID]
   )
(
SELECT 
   [OrderID] ,
   [CustomerID], 
   [SalespersonPersonID]
FROM sales.orders O (NOLOCK)
Where orderid =2
)

目前，我正在嘗試將三個視圖組合到一個儲存過程中以生成報告。起初，我開始對公共數據欄位使用連接，但發現其中一個表“XFER”包含導致重複數據的數據。

那麼，問題是該XFER表必須為每個order_no. 這就是問題所在，而不是您用來組合它們的任何方法（JOIN 與 VIEWS 與其他任何方法）。

只有您能說出為什麼會這樣，即使您已經描述了一些架構，我也不像您一樣了解您的數據。

因此，根據每個有多個對應行的原因，您可以嘗試以下方法之一：order_no``XFER

**1）**不要XFER直接加入，而是使用 GROUP BY 加入子查詢，保證每個訂單只返回一行，就像（只是猜測其他一些可能的列）：

SELECT order_no, COUNT(*) as xfer_count, MAX(xfer_date) AS last_xfer
FROM XFER
GROUP BY order_no

**2)**使用 WHERE 子句排除XFER不想要的行。同樣，不確定架構，但如果有一些列標識單個“活動”行，而不是其他過時的行order_no，則對其進行過濾：

SELECT order_no, ...
FROM XFER
WHERE status = 'Active'

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

相關問答

Sql-Server

SQL Server 中的臨時表和表變數有什麼區別？

  • February 2, 2022
檢視問答
Sql-Server

使用 OR 條件連接兩個臨時表時減少臨時表掃描

  • September 22, 2021
檢視問答
T-Sql

使用 ORDER BY 從 SELECT 創建 Azure Synapse 臨時表

  • May 1, 2021
檢視問答
Sql-Server

觸發器未更新

  • May 7, 2020
檢視問答
Sql-Server

使用 INSERT INTO…EXEC AT 連結伺服器進入臨時表失敗並顯示 Msg 7391

  • February 28, 2020
檢視問答
T-Sql

SQL Server 2000 數據庫監控

  • February 24, 2020
檢視問答