如何使用Group By子句合并两个sql server表？

Question

我有两个表TableA和TableB，如下所示：

TableA:

     ItemID          Qty           Rate
    --------        -----         --------
       1             10            100.00
       2             20            150.00

TableB:

     ItemID          Qty           Rate
    --------        -----         -------
       1              5            150.00
       3              10           200.00
       3              20           400.00

现在我想合并这两个表。我想要的结果需要如下：

结果 TableA：

     ItemID           Qty           Rate
    --------         -----         -------
       1              15            150.00
       2              20            150.00
       3              30            400.00

我尝试​​了以下 Insert Select 语句，但它没有给出所需的结果。

INSERT INTO TableA
(
     ItemID,
     Qty,
     Rate               
)
SELECT 
    ItemID, 
    SUM(Qty), 
    MAX(Rate)
FROM 
    TableB
GROUP BY 
    ItemID

但它给出的结果如下：

     ItemID          Qty           Rate
    --------        -----         --------
       1             10            100.00
       2             20            150.00
       1              5            150.00
       3              30           400.00

如何达到我想要的结果？

我尝试这样：

 MERGE PUR_PODetail AS Target
                USING (
                    SELECT 
                        @POID,
                        ItemID,
                        SUM(POQuantity),
                        MAX(UnitRate),
                        1,
                        CASE WHEN D1 = '' THEN NULL ELSE D1 END D1,
                        CASE WHEN D2 = '' THEN NULL ELSE D2 END D2,
                        CASE WHEN D3 = '' THEN NULL ELSE D3 END D3,
                        CASE WHEN RandomDimension = '' THEN NULL ELSE RandomDimension END RandomDimension,
                        0
                    FROM 
                        @Detail
                    GROUP BY 
                        ItemID, D1, D2, D3, RandomDimension
                    ) AS Source  ON (Target.ItemID = Source.ItemID) AND 
                    (ISNULL(Target.D1, -999) = ISNULL(Source.D1, -999)) AND 
                    (ISNULL(Target.D2, -999) = ISNULL(Source.D2, -999)) AND 
                    (ISNULL(Target.D3, -999) = ISNULL(Source.D3, -999)) AND
                    (ISNULL(Target.RandomDimension, -999) = ISNULL(Source.RandomDimension, -999))
                WHEN MATCHED
                    THEN UPDATE SET 
                            Target.POQuantity = Target.POQuantity + Source.POQuantity,
                            Target.UnitRate = MAX(Source.UnitRate)
                WHEN NOT MATCHED
                    INSERT
                        (
                            POID,
                            ItemID,
                            POQuantity,
                            UnitRate,
                            ItemStatusID,
                            D1,
                            D2,
                            D3,
                            RandomDimension,
                            EDInclusive_f
                        )
                    VALUES
                        (
                            @POID, 
                            Source.ItemID, 
                            Source.POQuantity, 
                            Source.UnitRate, 
                            1,
                            CASE WHEN Source.D1 = '' THEN NULL ELSE Source.D1 END D1,
                            CASE WHEN Source.D2 = '' THEN NULL ELSE Source.D2 END D2,
                            CASE WHEN Source.D3 = '' THEN NULL ELSE Source.D3 END D3,
                            CASE WHEN Source.RandomDimension = '' THEN NULL ELSE Source.RandomDimension END RandomDimension,
                            0
                        )

但它给出了以下错误。 请更正错误。我不知道这里会出什么问题。

消息 102，级别 15，状态 1，过程 PUR_PurchaseOrder_IU，第 936 行 “MERGE”附近的语法不正确。 消息 156，级别 15，状态 1，过程 PUR_PurchaseOrder_IU，第 953 行 关键字“AS”附近的语法不正确。

但是当我从存储过程中删除这些合并语句时，它正在执行......

Answer 1

为此，您不能仅使用 INSERT 语句，您必须 INSERT 或 UPDATE，具体取决于目标中已存在的 ItemID桌子。

SQL Server 2005

UPDATE  @TableA
SET     Qty = a.Qty + b.Qty
        , Rate = CASE WHEN a.Rate < b.Rate
                      THEN b.Rate 
                      ELSE a.Rate 
                  END
FROM    @TableA a
        INNER JOIN (
          SELECT  ItemID
                  , Qty = SUM(Qty)
                  , Rate = MAX(Rate)
          FROM    @TableB
          GROUP BY 
                  ItemID  
        ) b ON a.ItemID = b.ItemID

INSERT INTO @TableA
SELECT  ItemID, Qty, Rate
FROM    ( SELECT  ItemID
                  , Qty = SUM(Qty)
                  , Rate = MAX(Rate)
          FROM    @TableB b
          WHERE   NOT EXISTS (SELECT * FROM @TableA a WHERE a.ItemID = b.ItemID)
          GROUP BY 
                  ItemID  
        ) b

SQL Server 2008 提供MERGE 对此的声明。

根据与源表的联接结果对目标表执行插入、更新或删除操作。例如，你
可以通过插入、更新或删除行来同步两个表
一个表基于另一个表中发现的差异。

SQL Server 2008

MERGE @TableA AS Target
USING (
  SELECT  ItemID
          , Qty = SUM(Qty)
          , Rate = MAX(Rate)
  FROM    @TableB
  GROUP BY 
          ItemID  
) AS source (ItemID, Qty, Rate) ON (target.ItemID = source.ItemID)
WHEN MATCHED THEN 
  UPDATE SET target.Qty = target.Qty + source.Qty
             , target.Rate = CASE WHEN target.Rate < source.Rate 
                                  THEN source.Rate 
                                  ELSE target.Rate 
                             END
WHEN NOT MATCHED THEN
  INSERT (ItemID, Qty, Rate)
  VALUES (source.ItemID, source.Qty, source.Rate);

Answer 2

试试这个：

MERGE TableA T
USING
(
  SELECT ItemId, SUM(Qty) Qty, MAX(Rate) Rate
  FROM
  (
   SELECT ItemId, Qty, Rate from TableA
   UNION ALL
   SELECT ItemId, Qty, Rate from TableB
) S
ON T.ItemId = S.ItemId
WHEN MATCHED THEN UPDATE SET
  Qty = S.Qty,
  Rate= S.Rate
WHEN NOT MATCHED THEN 
  INSERT(ItemId, Qty, Rate)
  VALUES(S.ItemId, S.Qty, S.Rate);

Answer 3

声明 @t 表 (ItemID int,Qty int,Rate int)
插入 @t 值 (1,10,100),(2,20,150)

Select * from @t

声明 @t1 表 (ItemID int,Qty int,Rate int)
插入@t1值 (1,5,150),(3,10,200),(3,20,400)

从@t1中选择*

插入@t
从 @t1 中选择 ItemID,sum(Qty) Qty,max(Rate) Rate，其中 ItemID 不在（从 @t 中选择 ItemID）
按 ItemID 分组

从 @t 中选择 *