Блог

Главная - Вопросы по программированию - SQL — Как сравнить данные столбца при итерации строка за строкой и вставить новую строку, если они не совпадают

SQL — Как сравнить данные столбца при итерации строка за строкой и вставить новую строку, если они не совпадают

#sql-server

#sql-сервер

Вопрос:

У меня есть следующие данные в таблице SQL 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 строки между ними таким образом, чтобы статус 1-й вставленной строки для этой предыдущей был «Выполняется» и true, а 2-я вставленная строкастатус будет прекращен и false, POS_ID а код термина обеих этих 2 вставленных строк будет кодом термина новой 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 для проверки следующего значения в строке для столбца, но не уверен, как выполнить логику реализации, как указано выше, если значения не совпадают, вставьте 2 новые строки

 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)

Ответ №1:

Объединение функций lead() and 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 и пошаговым построением.

Комментарии:

1. Спасибо @Sander, он выдает вывод, но последовательность данных не соответствует ожидаемому результату, даже не совпадающему с результатом, которым вы поделились, поэтому вновь вставленная строка TR, т.Е. прерывание, не появляется после TR, я думаю, это в основном неправильно сортирует данные.

2. Я добавил «необычную» сортировку в конце моей скрипки, чтобы получить данные в том же порядке, что и ожидаемый результат, для упрощения проверки результата. Что происходит, так это то, что новые строки получают новые автоматические uniqueid значения. Отсутствие какой-либо сортировки, скорее всего, вернет данные в соответствии с uniqueid полем (новые строки заканчиваются в конце). Возня со значениями IDENTIY(1,1) столбца — это совсем другая проблема! Тот, который заслуживает другого вопроса StackOverflow…

3. Понял, спасибо. Но что, если мои значения в столбце POS_ID не ограничены только 3 значениями (TR, CA, DA), скажем, в фактических данных будет намного больше таких значений в таком сценарии, который ниже, не будет работать правильно? Порядок по регистру md. POS_ID когда ‘TR’, затем 1, когда ‘CA’, затем 2, когда ‘DA’, затем 3 end

4. Посмотрите на это для решения сортировки.

Ответ №2:

Новые строки, которые нужно вставить, основаны на задержке (pos_id), упорядоченной по term_code . Где задержка (pos_id)<>pos_id затем вставьте 2 строки.

Использование предоставленных образцов данных (в виде временной таблицы)

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

Вставить statment

 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 0

Ответ №3:

Я думаю, что это может помочь :

 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]

Этот скрипт просто генерирует новые строки, если вы хотите, вы можете объединить их своими данными

в результате некоторые думают примерно так

Результат

Ответ №4:

Спасибо, @SteveC и @Sander Я использовал комбинацию запросов, которые были общими для вас обоих, для получения конечного результата.

Разделяем окончательный запрос здесь, поэтому он может быть полезен для тех, у кого возникнут аналогичные требования в будущем.

 ;with lag_cte as (
     select *, lag(pos_id) over (order by term_code) lag_pos
     from dbo.input_Main_data)
insert dbo.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;


-- to print
;with cte_sort as
(
  select md.POS_ID,
         min(md.uniqueid) as sort_num
  from dbo.input_Main_data md
  group by md.POS_ID
)
select md.POS_ID,
       md.Term_Code,
       md.Status,
       md.IsActive
from dbo.input_Main_data md
join cte_sort s
  on s.POS_ID = md.POS_ID
order by s.sort_num,
         md.Term_Code,
         md.IsActive desc;