Блог

Главная - Вопросы по программированию - SQL Server: удалить предыдущие строки на основе значения в текущей строке

SQL Server: удалить предыдущие строки на основе значения в текущей строке

#sql #sql-server #tsql

#sql #sql-server #tsql

Вопрос:

У меня есть таблица со следующими столбцами: Имя автора, страна, статус.

     Author_name       country       status
-------------------------------------------
1   Author - 1  Country - 1 name    remove
2   Author - 2  Country - 2 name    done
3   Author - 3  Country - 3 name    done
4   Author - 4  Country - 4 name    done
5   Author - 5  Country - 5 name    done
6   Author - 6  Country - 6 name    remove
7   Author - 7  Country - 7 name    remove
8   Author - 8  Country - 8 name    done
9   Author - 9  Country - 9 name    remove
10  Author - 10 Country - 10 name   done

Ожидаемая функциональность: при обнаружении «удалить» эта строка должна быть удалена, а строка над ней должна быть удалена.

Если «удалить» отображается последовательно, то необходимо удалить столько строк сверху. местоположение ‘remove’ и количество записей не являются постоянными.

После запуска SQL-скрипта вывод приведенной выше таблицы должен быть: 

      Author_name      country        status
-------------------------------------------
2    Author - 2  Country - 2 name    done
3    Author - 3  Country - 3 name    done
10   Author - 10 Country - 10 name   done

Надеюсь, вы поняли логику.

Я также пробовал использовать лаг, курсор и некоторые другие. Теперь я пытаюсь использовать цикл while. Пожалуйста, помогите мне с этим. Вот мой последний код.

Моей базой данных является SQL Server, и в настоящее время я использую онлайн-компилятор:https://rextester.com/l/sql_server_online_compiler

 CREATE Table tblAuthors
(
   Author_name nvarchar(50),
   country nvarchar(50),
   prod varchar(50),
   timestamp datetime 
)

Declare @Id int
Set @Id = 1

While @Id <= 10
Begin 
   Insert Into tblAuthors values ('Author - '   CAST(@Id as nvarchar(10)),
              'Country - '   CAST(@Id as nvarchar(10))   ' name', 'done', getdate())
   Print @Id
   Set @Id = @Id   1
End

update tblAuthors set prod = 'remove' where  Author_name  = 'Author - 1'
update tblAuthors set prod = 'remove' where  Author_name  = 'Author - 6'
update tblAuthors set prod = 'remove' where  Author_name  = 'Author - 7'
update tblAuthors set prod = 'remove' where  Author_name  = 'Author - 9'

SELECT * FROM tblAuthors

select IDENTITY(int, 1,1) as idnt,*  into ##temp_tblAuthors from tblAuthors

select * from ##temp_tblAuthors


DECLARE @intFlag INT = 1
DECLARE @Flag INT = 0
DECLARE  @Author_name nvarchar(50)
DECLARE @country nvarchar(50)
DECLARE  @prod varchar(50)
DECLARE @idnt INT

select @Flag = count(*) from ##temp_tblAuthors

WHILE (@intFlag <=@Flag)
BEGIN

select 
 @idnt = idnt
,@Author_name = Author_name
,@country = country
,@prod = prod 
from ##temp_tblAuthors where idnt = @intFlag

if @prod = 'remove' 
BEGIN 

SELECT  @intFlag
select 
 @idnt = idnt
,@Author_name = Author_name
,@country = country
,@prod = prod 
from ##temp_tblAuthors where idnt = @intFlag-1

DELETE tblAuthors WHERE 
Author_name = @Author_name AND 
country = @country  AND 
prod  = @prod

select 
 @idnt = idnt
,@Author_name = Author_name
,@country = country
,@prod = prod 
from ##temp_tblAuthors where idnt = @intFlag

DELETE tblAuthors WHERE 
Author_name = @Author_name AND 
country = @country  AND 
prod  = @prod



END 

SET @intFlag = @intFlag   1
END
GO

SELECT * FROM tblAuthors

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

1. Наборы данных в SQL явно неупорядочены . Нет «строки выше». Вам нужен столбец (или колонок) , который вы можете использовать в ORDER BY для утверждения порядка. У вас есть такой столбец? (Например, временная метка, указывающая, когда была вставлена строка?)

2. Привет @MatBailie да, есть столбец identity, надеюсь, мы сможем использовать то же самое.

3. Не гарантируется , что столбец идентификаторов находится в порядке, для строки возможно , что значение идентификатора ниже, чем у ранее существовавшей строки. (Рекомендуется никогда ничего не выводить из столбца identity и использовать его только как уникальный идентификатор) Есть ли у вас другие столбцы?

4. @MatBailie .. спасибо за ответ .. В настоящее время я разрабатываю базу данных, я могу добавить столбец временных меток. надеюсь, это решит проблему с порядком.

5. Добавлен столбец временной метки @MatBailie… Я все еще пытаюсь разобраться в процессе удаления.

Ответ №1:

Если вы работаете в обратном направлении, вы можете суммировать «оценку», где 'remove' имеет -1 и 'done' имеет 1 .

     Author_name   Country             Status    Score
------------------------------------------------------
1   Author - 1    Country - 1 name    remove     2
2   Author - 2    Country - 2 name    done       3
3   Author - 3    Country - 3 name    done       2
4   Author - 4    Country - 4 name    done       1
5   Author - 5    Country - 5 name    done       0
6   Author - 6    Country - 6 name    remove    -1
7   Author - 7    Country - 7 name    remove     0
8   Author - 8    Country - 8 name    done       1
9   Author - 9    Country - 9 name    remove     0
10  Author - 10   Country - 10 name   done       1   <- Start from here

Пример SQL:

 SELECT
  *,
  SUM(CASE WHEN status = 'remove' THEN -1 ELSE 1 END)
    OVER (ORDER BY timestamp DESC, id DESC)
      AS score
FROM
  tblAuthors

Затем вы хотите сохранить только те строки, в которых (все еще работая в обратном направлении) оценка выше, чем когда-либо была до сих пор.

     Author_name   Country             Status    Score   PreviousMax
--------------------------------------------------------------------
1   Author - 1    Country - 1 name    remove     2      3
2   Author - 2    Country - 2 name    done       3      2    <- KEEP
3   Author - 3    Country - 3 name    done       2      1    <- KEEP 
4   Author - 4    Country - 4 name    done       1      1
5   Author - 5    Country - 5 name    done       0      1
6   Author - 6    Country - 6 name    remove    -1      1
7   Author - 7    Country - 7 name    remove     0      1
8   Author - 8    Country - 8 name    done       1      1
9   Author - 9    Country - 9 name    remove     0      1
10  Author - 10   Country - 10 name   done       1      NULL <- KEEP

Вы можете сделать это с помощью…

 WITH
  scored_rows AS
(
  SELECT
    *,
    SUM(CASE WHEN status = 'remove' THEN -1 ELSE 1 END)
      OVER (ORDER BY timestamp DESC, id DESC)
        AS score
  FROM
    tblAuthors
),
  checked_scored_rows AS
(
  SELECT
    *,
    MAX(score)
      OVER (ORDER BY timestamp DESC, id DESC
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           )
             AS previous_max_score
  FROM
    scored_rows
)
SELECT
  *
FROM
  checked_scored_rows
WHERE
  score > COALESCE(previous_max_score, 0)
;

Это тоже приводит вас…

 WITH
  scored_rows AS
(
  SELECT
    *,
    SUM(CASE WHEN status = 'remove' THEN -1 ELSE 1 END)
      OVER (ORDER BY timestamp DESC, id DESC)
        AS score
  FROM
    tblAuthors
),
  checked_scored_rows AS
(
  SELECT
    *,
    MAX(score)
      OVER (ORDER BY timestamp DESC, id DESC
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           )
             AS previous_max_score
  FROM
    scored_rows
)
DELETE
  tblAuthors
FROM
  checked_scored_rows
WHERE
      checked_scored_rows.id     = tblAuthors.id
  AND checked_scored_rows.score <= COALESCE(checked_scored_rows.previous_max_score, 0)

Я считаю, что это демонстрирует то, что вы хотите: https://rextester.com/ZNNB32798