logo标签列表

SQL 更新表格列为一系列数值

sqlsql-server
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      |    |        |        |
|    |         | 5  | ITEME  | 2      |    |        |        |
|    |         | 6  | ITEMF  | 2      |    |        |        |
--------------------------------------------------------------

在这里，我需要从子项目中复制并插入回来，但是要使用TYPEID为2的ITEMIDs，结果如下所示：

--------------------------------------------------------------
|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关系的行才应该被考虑（因为这是其中最少的计数）如下例所示。

--------------------------------------------------------------
|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      |    |        |        |
--------------------------------------------------------------

当然，实际数据并不是那么简单，有许多其他类型和项目的子项目，所需的ID可能会缺少一些序列，例如10001、10008、40042等，还有许多其他列定义了正在复制的数据以及哪些ID需要覆盖它们。重要的是如何将每个数据行与每个获得的ID进行1:1映射（假设在此合并时它们都在自己的临时表中）。以下是我目前能够做到的示例：
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”。

你只需要为 TYPEID = 2 进行插入吗？如果类型有不同数量的项目会怎样？ - uzi
目前是针对TYPEID = 2的情况，是的，它们在这种情况下有不同数量的行...有1065个子项需要复制，当然还有30294个TYPEID = 2的项目中的1065个项目。 - Musab M. Jafri
它们应该如何插入？在问题中展示样例。 - uzi
这就是实际问题，如何将它们插入在一起，使得第二个示例得以实现。第二个表格表示是期望的最终结果。我想我应该将这个声明添加到问题中。 - Musab M. Jafri
我知道那是你期望的输出。只需在“items”表中为TypeId = 2添加新行即可。并展示这样的数据将会产生什么期望结果。 - uzi
在TypeID = 2的Items表中不会有第四行，只有两者数量相等的行受到影响，因为数据必须是1:1的。我也会在问题中更新这种情况。 - Musab M. Jafri
1个回答
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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接