SQL 使用值序列更新表列

Question

我遇到这样一种情况，我需要在其中一个列中使用不同范围的外键在其内部创建一个表的数据副本。例如:

--------------------------------------------------------------
|TYPES         |ITEMS                 |SUBITEMS              |
|--------------|----------------------|----------------------|
| ID | VALUE   | ID | VALUE  | TYPEID | ID | VALUE  | ITEMID |
|----|---------|----|--------|--------|----|--------|--------|
| 1  | TYPE1   | 1  | ITEMA  | 1      | 1  | SUB1   | 1      |
| 2  | TYPE2   | 2  | ITEMB  | 1      | 2  | SUB2   | 2      |
|    |         | 3  | ITEMC  | 1      | 3  | SUB3   | 3      |
|    |         | 4  | ITEMD  | 2      |    |        |        |
|    |         | 5  | ITEME  | 2      |    |        |        |
|    |         | 6  | ITEMF  | 2      |    |        |        |
--------------------------------------------------------------

在这里，我必须从 SUBITEMS 复制并插入回来，但 ITEMID 的 TYPEID 为 2，导致以下示例:

--------------------------------------------------------------
|TYPES         |ITEMS                 |SUBITEMS              |
|--------------|----------------------|----------------------|
| ID | VALUE   | ID | VALUE  | TYPEID | ID | VALUE  | ITEMID |
|----|---------|----|--------|--------|----|--------|--------|
| 1  | TYPE1   | 1  | ITEMA  | 1      | 1  | SUB1   | 1      |
| 2  | TYPE2   | 2  | ITEMB  | 1      | 2  | SUB2   | 2      |
|    |         | 3  | ITEMC  | 1      | 3  | SUB3   | 3      |
|    |         | 4  | ITEMD  | 2      | 4  | SUB1   | 4      |
|    |         | 5  | ITEME  | 2      | 5  | SUB2   | 5      |
|    |         | 6  | ITEMF  | 2      | 6  | SUB3   | 6      |
--------------------------------------------------------------

编辑 2:如果任一表中的行数不同(4 个项目而 3 个子项目或 3 个项目而 4 个子项目)，则应仅考虑那些足以在两个表之间形成 1:1 关系的行(3 个结果，因为这是其中最少的结果)如下例所示。

--------------------------------------------------------------
|TYPES         |ITEMS                 |SUBITEMS              |
|--------------|----------------------|----------------------|
| ID | VALUE   | ID | VALUE  | TYPEID | ID | VALUE  | ITEMID |
|----|---------|----|--------|--------|----|--------|--------|
| 1  | TYPE1   | 1  | ITEMA  | 1      | 1  | SUB1   | 1      |
| 2  | TYPE2   | 2  | ITEMB  | 1      | 2  | SUB2   | 2      |
|    |         | 3  | ITEMC  | 1      | 3  | SUB3   | 3      |
|    |         | 4  | ITEMD  | 2      | 4  | SUB1   | 4      |
|    |         | 5  | ITEME  | 2      | 5  | SUB2   | 5      |
|    |         | 6  | ITEMF  | 2      | 6  | SUB3   | 6      |
|    |         | 7  | ITEMG  | 2      |    |        |        |
--------------------------------------------------------------

当然，实际数据并不那么简单，还有许多其他类型和项目 n 个子项目，所需的 ID 会缺少一些序列，如 10001、10008、40042 等，许多其他列都定义了要复制的数据以及哪些 ID 需要覆盖它们。这只是获得的每个数据行应该如何以 1:1 的比例映射到获得的每个 ID 的问题(假设在合并之前两者都在它们自己的临时表中)。以下是我目前能够做的示例:

CREATE TABLE #SubItemsTemp (Value VARCHAR(100))
CREATE TABLE #ItemIDsTemp (TypeID INT)

INSERT INTO #SubItemsTemp (Value)
SELECT
    SI.Value
FROM
    SubItems SI
    JOIN Items IT ON SI.ItemID = IT.ID
WHERE
    IT.TypeID = 1

INSERT INTO #ItemIDsTemp(Value)
SELECT IT.ID
FROM Items IT
WHERE IT.TypeID = 2

--What next?

编辑 1:忘记提及实际的问题行......如何将它们一起插入到 SUBITEMS 表中，以便第二个示例得以实现？

脚注:这是对具有多个连接以获取“TYPE”的实际查询的极端简化

Answer 1

试试这个查询。查询假定 SUBITEMS 表中的 ID 列是标识，并且仅适用于 TypeId 的 1 和 2

declare @TYPES table(ID int, VALUE varchar(100))
declare @ITEMS table(ID int, VALUE varchar(100), TYPEID int)
declare @SUBITEMS table(ID int identity(1,1), VALUE varchar(100), ITEMID int)

insert into @TYPES values (1, 'TYPE1'), (2, 'TYPE2')
insert into @ITEMS values (1, 'ITEMA', 1), (2, 'ITEMB', 1), (3, 'ITEMC', 1), (4, 'ITEMD', 2), (5, 'ITEME', 2), (6, 'ITEMF', 2), (7, 'ITEMG', 2)
insert into @SUBITEMS values ('SUB1', 1), ('SUB2', 2), ('SUB3', 3)

; with cte_1 as (
    select 
        s.VALUE, rn = row_number() over (order by i.ID)
    from 
        @ITEMS i
        join @SUBITEMS s on s.ITEMID = i.ID
    where
        i.TYPEID = 1
)
, cte_2 as (
    select
        ID, rn = row_number() over (order by ID)
    from
        @ITEMS
    where
        TYPEID = 2
)

insert into @SUBITEMS
select
    a.VALUE, b.ID
from
    cte_1 a
    join cte_2 b on a.rn = b.rn

select * from @SUBITEMS

输出

ID  Value   ItemId
------------------
1   SUB1    1
2   SUB2    2
3   SUB3    3
4   SUB1    4
5   SUB2    5
6   SUB3    6