主页 / dba / 问题 / 173434
Accepted
tfenwick11
Asked: 2017-05-13 05:46:17 +0800 CST

将共享公共列的多个查询结果集组合到一个指定的临时表中

  • 772

目前，我正在尝试将三个视图组合到一个存储过程中以生成报告。起初，我开始对公共数据字段使用连接，但发现其中一个表 ,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())
sql-server-2000 t-sql

3 个回答

  1. 你的问题是你的临时表#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
)
    • 4

  2. 目前，我正在尝试将三个视图组合到一个存储过程中以生成报告。起初，我开始对公共数据字段使用连接，但发现其中一个表“XFER”包含导致重复数据的数据。

    那么，问题是该XFER表必须为每个order_no. 这就是问题所在，而不是您用来组合它们的任何方法（JOIN 与 VIEWS 与其他任何方法）。

    只有您能说出为什么会这样，即使您已经描述了一些架构，我也不像您一样了解您的数据。

    因此，根据每个有多个对应行的原因，您可以尝试以下方法之一：order_noXFER

    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'
    • 3
  3. Best Answer

    将我的代码标记为答案，以防有人需要查看之前代码中的语法以了解其外观。@SqlWorldWide 确实以是否可以将三个选择的结果放入一个临时表中的形式回答了这个问题，他的示例确实对我有用。但作为一种更好的做法，我最初想做的是由@BradC 定义的。他的回答是一种更好的做法，并且比使用现有的三个视图而不是使用连接方法更简单。感谢您的回答，他们都非常有帮助！

            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
    (
      order_no
    , freight_allow_type
    , shipped 
    )
        (
           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
    (
                 order_no 
               , carton_no 
               , number_of_boxes 
               , skid_height_inches 
               , dim_ext_x 
               , dim_ext_y 
               , product_weight 
               , pallet_weight 
               , total_pallet_weight
               , cubic_feet 
               , PCF 
    )




          (
            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 
                    --THEN CONVERT(DECIMAL(10,4), (TCT.weight + 30) / ((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'
            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
    (
              routing 
             , bol_no 
             , order_no
             , ext 
             , ship_to_name 
             , ship_to_add_3 
             , [location] 
             , cust_code 
             --, date_shipped 
             , skids 
             , [weight] 
             , BOL_est_freight 
    )

         (
               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())
    )
    • 0

相关问题