SQL -如何在逐行迭代时比较列的数据，并在不匹配的情况下插入新行
SQL -如何在逐行迭代时比较列的数据，并在不匹配的情况下插入新行
提问于 2020-11-10 19:07:14
Server 2017表中有以下数据：
POS_ID Term Code Status IsActive
----------------------------------------------------
TR 101 In Progress true
TR 102 In Progress true
TR 103 In Progress true
CA 151 In Progress true
CA 152 In Progress true
DA 161 In Progress true要求是迭代每一行，并将当前行的POS_ID与前一行进行比较，如果找到了不同的POS_ID，则希望在其中插入2行，使前一个POS_ID的第一个插入行的状态为“正在进行”，并终止true和第二个插入行的状态，并且这两个插入行的术语代码都是当前行的新POS_ID的术语代码。
类似于本例中的内容：
POS_ID Term Code Status IsActive
---------------------------------------------------------
TR 101 In Progress true
TR 102 In Progress true
TR 103 In Progress true
TR 151 In Progress true -- NEW ROW
TR 151 discontinue false -- NEW ROW
CA 151 In Progress true
CA 152 In Progress true
CA 161 In Progress true -- NEW ROW
CA 161 discontinue false -- NEW ROW
DA 161 In Progress true我尝试使用Lead函数检查列行中的下一个值，但不确定如果值不匹配插入两个新行，如何实现上面提到的逻辑。
SELECT
POS_ID AS currentvalue,
LEAD(POS_ID) OVER (ORDER BY uniqueid) AS NextValue
FROM
dbo.input_Main_data创建表并插入脚本
CREATE TABLE dbo.input_Main_data
(
UniqueId bigint identity(1,1),
POS_ID varchar(10),
Term_code bigint,
Status varchar(50),
IsActive bit,
CONSTRAINT PK_input_Main_data_UniqueId
PRIMARY KEY (UniqueId)
)
GO
INSERT INTO dbo.input_Main_data([POS_ID], [Term_code], [Status], [IsActive])
VALUES ('TR', 101, 'IN_PROGRESS', 1), ('TR', 102, 'IN_PROGRESS', 1),
('TR', 103, 'IN_PROGRESS', 1), ('CA', 151, 'IN_PROGRESS', 1),
('CA', 152, 'IN_PROGRESS', 1), ('DA', 161, 'IN_PROGRESS', 1)回答 4
Stack Overflow用户
回答已采纳
发布于 2020-11-10 20:31:24
要插入的新行基于term_code排序的延迟(Term_code)。其中，延迟(Pos_id)<>pos_id插入2x行。
使用提供的示例数据(作为临时表)
drop table if exists #input_Main_data;
go
Create table #input_Main_data
( UniqueId bigint identity(1,1),
POS_ID varchar(10),
Term_code bigint,
Status varchar(50),
IsActive bit,
CONSTRAINT PK_input_Main_data_UniqueId PRIMARY KEY (UniqueId));
go
INSERT INTO #input_Main_data([POS_ID],[Term_code], [Status], [IsActive]) Values
('TR',101,'IN_PROGRESS',1), ('TR',102,'IN_PROGRESS',1), ('TR',103,'IN_PROGRESS',1),
('CA',151,'IN_PROGRESS',1),('CA',152,'IN_PROGRESS',1),('DA',161,'IN_PROGRESS',1);插入状态
with lag_cte as (
select *, lag(pos_id) over (order by term_code) lag_pos
from #input_Main_data)
insert #input_Main_data
select lag_pos, term_code, [status], isactive
from lag_cte
where pos_id<>lag_pos
union all
select lag_pos, term_code, 'discontinue', 0
from lag_cte
where pos_id<>lag_pos;查询( INSERT语句后)
select [POS_ID],[Term_code], [Status], [IsActive]
from #input_Main_data
order by pos_id desc, term_code, isactive desc;输出
POS_ID Term_code Status IsActive
TR 101 IN_PROGRESS 1
TR 102 IN_PROGRESS 1
TR 103 IN_PROGRESS 1
TR 151 IN_PROGRESS 1
TR 151 discontinue 0
DA 161 IN_PROGRESS 1
CA 151 IN_PROGRESS 1
CA 152 IN_PROGRESS 1
CA 161 IN_PROGRESS 1
CA 161 discontinue 0Stack Overflow用户
发布于 2020-11-10 19:57:20
将lead()和lag()函数与公共表表达式(CTE)组合在一起，一些case表达式和一个insert语句应该这样做。
样本数据
create table MyData
(
uniqueid bigint IDENTITY(1,1),
POS_ID nvarchar(2),
Term_Code int,
Status nvarchar(15),
IsActive bit
);
insert into MyData (POS_ID, Term_Code, Status, IsActive) values
('TR', 101, 'In Progress', 1),
('TR', 102, 'In Progress', 1),
('TR', 103, 'In Progress', 1),
('CA', 151, 'In Progress', 1),
('CA', 152, 'In Progress', 1),
('DA', 161, 'In Progress', 1);溶液
with cte as
(
select md.*,
coalesce(lead(md.POS_ID) over(order by md.uniqueid), md.POS_ID) as prev_POS_ID,
coalesce( lag(md.POS_ID) over(order by md.uniqueid), md.POS_ID) as next_POS_ID,
coalesce(lead(md.Term_Code) over(order by md.uniqueid), md.Term_Code) as prev_Term_Code
from MyData md
)
insert into MyData (POS_ID, Term_Code, Status, IsActive)
select c.next_POS_ID as POS_ID,
case when c.POS_ID <> c.prev_POS_ID then c.prev_Term_Code else c.Term_Code end as Term_Code,
case when c.POS_ID <> c.prev_POS_ID then c.Status else 'discontinue' end as Status,
case when c.POS_ID <> c.prev_POS_ID then c.IsActive else 0 end as IsActive
from cte c
where c.POS_ID <> c.prev_POS_ID
or c.POS_ID <> c.next_POS_ID;结果
POS_ID Term_Code Status IsActive
------ --------- ----------- --------
TR 101 In Progress True
TR 102 In Progress True
TR 103 In Progress True
TR 151 In Progress True
TR 151 discontinue False
CA 151 In Progress True
CA 152 In Progress True
CA 161 In Progress True
CA 161 discontinue False
DA 161 In Progress True小提琴与中间CTE的结果和逐步建设。
Stack Overflow用户
发布于 2020-11-10 20:22:59
我认为这能帮上忙：
SELECT [POS_ID],[Term Code],[Status],IsActive,[Priority] from(
select [UniqueId]
,[POS_ID]
,[Term Code]
,c.[Status]
,IsActive
,Last_Pos_ID
,c.[Priority]
from(
Select * from(
SELECT [UniqueId]
,[POS_ID]
,[Term Code]
,[Status]
,IsActive
,LAG([POS_ID],1) over(order by [UniqueId]) Last_Pos_ID
FROM dbo.input_Main_data) tmp
where Last_Pos_ID is not null and Last_Pos_ID <>[POS_ID] )b
cross apply
(
select 'In Progress' [Status],0 [Priority]
union
select 'discontinue' [Status],1 [Priority]
) c
) d
order by [UniqueId],[Priority]这个脚本jus生成新的行，如果您想要根据数据合并它的话。
结果就是有人这样想
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64775215
复制相关文章
相似问题
腾讯云开发者
