如何为无重复临时表temp1填充重复表sup_00063186105的首个ItemID

已通过以下语句向无重复临时表temp1插入了去重后的StoreID、SaleID、SKU数据：

insert into temp1(StoreID,SaleID, SKU)
select distinct StoreID,SaleID,SKU from sup_00063186105

现在需要将重复表sup_00063186105中每组（StoreID、SaleID、SKU）对应的首个ItemID，填充到temp1的ItemID字段中。尝试使用MERGE INTO和UPDATE操作时因源表重复数据受阻，且不希望通过PL/SQL实现需求。

两张表结构及sup_00063186105的测试数据插入语句如下：

CREATE TABLE temp1 (
  StoreID INT,
  SaleID INT,
  ItemID INT,
  SKU VARCHAR(10)
);

CREATE TABLE sup_00063186105 (
  StoreID INT,
  SaleID INT,
  ItemID INT,
  SKU VARCHAR(10)
);
INSERT INTO sup_00063186105 (StoreID, SaleID, ItemID, SKU)
VALUES (8245, 48699, 486991001, '235060P');
INSERT INTO sup_00063186105 (StoreID, SaleID, ItemID, SKU)
VALUES (8245, 48699, 486991002, '235060P');
INSERT INTO sup_00063186105 (StoreID, SaleID, ItemID, SKU)
VALUES (8245, 48699, 486991002, '235060P');
INSERT INTO sup_00063186105 (StoreID, SaleID, ItemID, SKU)
VALUES (8245, 48699, 486991002, '250780P');
INSERT INTO sup_00063186105 (StoreID, SaleID, ItemID, SKU)
VALUES (8245, 48699, 486991002, '250781P');

核心思路是先对源表sup_00063186105分组处理，每组（StoreID、SaleID、SKU）只保留首个ItemID，再将处理后的结果关联到temp1完成更新。这里的“首个”可通过窗口函数ROW_NUMBER()按指定规则排序确定。

方法1：UPDATE结合子查询 #

先通过子查询获取每组的首个ItemID，再关联temp1执行更新：

UPDATE temp1 t
SET ItemID = (
    SELECT s.ItemID
    FROM (
        SELECT 
            StoreID, SaleID, SKU, ItemID,
            ROW_NUMBER() OVER (PARTITION BY StoreID, SaleID, SKU ORDER BY ItemID) AS rn
        FROM sup_00063186105
    ) s
    WHERE s.StoreID = t.StoreID 
      AND s.SaleID = t.SaleID 
      AND s.SKU = t.SKU 
      AND s.rn = 1
);

若希望按数据插入顺序取首个（无自增主键/时间戳时，Oracle可通过ROWID近似模拟），替换排序规则即可：

UPDATE temp1 t
SET ItemID = (
    SELECT s.ItemID
    FROM (
        SELECT 
            StoreID, SaleID, SKU, ItemID,
            ROW_NUMBER() OVER (PARTITION BY StoreID, SaleID, SKU ORDER BY ROWID) AS rn
        FROM sup_00063186105
    ) s
    WHERE s.StoreID = t.StoreID 
      AND s.SaleID = t.SaleID 
      AND s.SKU = t.SKU 
      AND s.rn = 1
);

方法2：MERGE INTO（先处理源表重复） #

先对源表分组去重，再用MERGE操作更新temp1：

MERGE INTO temp1 t
USING (
    SELECT 
        StoreID, SaleID, SKU, ItemID
    FROM (
        SELECT 
            StoreID, SaleID, SKU, ItemID,
            ROW_NUMBER() OVER (PARTITION BY StoreID, SaleID, SKU ORDER BY ItemID) AS rn
        FROM sup_00063186105
    )
    WHERE rn = 1
) s
ON (t.StoreID = s.StoreID AND t.SaleID = s.SaleID AND t.SKU = s.SKU)
WHEN MATCHED THEN
    UPDATE SET t.ItemID = s.ItemID;

方法3：一步插入（替代先插入去重再更新） #

若尚未向temp1插入数据，可直接插入去重后的全量数据（含首个ItemID）：

INSERT INTO temp1(StoreID, SaleID, SKU, ItemID)
SELECT StoreID, SaleID, SKU, ItemID
FROM (
    SELECT 
        StoreID, SaleID, SKU, ItemID,
        ROW_NUMBER() OVER (PARTITION BY StoreID, SaleID, SKU ORDER BY ItemID) AS rn
    FROM sup_00063186105
)
WHERE rn = 1;

内容的提问来源于stack exchange，提问作者OKEEngine