sql - CTE 递归选择并插入到具有新 id 和一些更新字段的同一个表中
问题描述
假设我有这张桌子:
|TeamId |Name |ActiveYear |ParentTeamId |OriginTeamId |
+-------+---------------+-----------+-------------+-------------+
|1 |Founding Team |2020 |NULL |NULL |
|2 |Elders Team |2020 |1 |NULL |
|3 |Younger Team |2020 |1 |NULL |
|4 |Women Team |2020 |2 |NULL |
|5 |Men´s Team |2020 |2 |NULL |
当我查询它时,我可以看到层次结构:
WITH abcd AS
(
-- anchor
SELECT
CAST((Convert(varchar(20), TeamId)) AS VARCHAR(1000)) AS "Tree",
TeamId, [Name], Activeyear, ParentTeamId, OriginTeamId
FROM
Teams AS p
WHERE
ParentTeamId IS NULL And ActiveYear = 2020
UNION ALL
--recursive member
SELECT
CAST((CONVERT(varchar(20), a.tree) + '/' + CONVERT(varchar(20), c.TeamId)) AS VARCHAR(1000)) AS "Tree",
c.TeamId, c.[Name], c.Activeyear, c.ParentTeamId, c.OriginTeamId
FROM
Teams AS c
JOIN
abcd AS a ON c.ParentTeamId = a.TeamId
WHERE
c.ActiveYear = 2020
)
SELECT *
FROM abcd
现在,我需要更新此表,重新创建相同的结构,但使用新的 ID 和 Activeyear = 2021。
因此,新的父记录仍将是其他新记录的父记录。
我想做一个查询,它允许我“克隆”这些数据并插入一些更新:ActiveYear = 2021, OriginTeamId(OriginTeamId is to identify where that information come from)
我怎样才能做到这一点?
解决方案
一种方法是分两个步骤。插入没有 ID 的行。然后找出id:
insert into teams (name, activeyear, parentid)
select name, 2021, parentid
from teams
where actdiveyear = 2020;
现在通过查找相应的 id 来更新值。窗口函数可以在这里提供帮助:
insert into teams (name, activeyear, ParentTeamId)
select name, 2021, ParentTeamId
from teams
where activeyear = 2020;
with corresponding as (
select t2020.teamid as teamid_2020, t2021.teamid as teamid_2021
from teams t2020 join
teams t2021
on t2020.name = t2021.name
where t2020.activeyear = 2020 and t2021.activeyear = 2021
)
update t
set ParentTeamId = c.teamid_2021
from teams t join
corresponding c
on t.ParentTeamId = c.teamid_2020
where t.activeyear = 2021;
这是一个 db<>fiddle。
推荐阅读
- android - 回收站视图未显示在片段中
- dart - 如何根据 Dart 中的匹配属性加入两个对象列表
- sql-server - 重新编译查询计划
- angular - 在 ngx-chips 中单击选项卡上的下拉列表项
- android - CandleDataSet 未在 Android 6.0.1 设备上绘制
- c# - 如何根据键值设置选中列表框中的项目?
- c# - 当有多个调用调用该方法时,一种锁定等待调用的方法?
- arrays - 通过比较数组 React JS 的元素来唯一的数组
- npm - 代码 EISGIT ... node_modules/sitemap:似乎是一个 git repo 或子模块
- php - 来自 env 的值没有回显