У меня есть данные, хранящиеся в таблице, скажем, T1. Я хочу группировать строки таким образом, чтобы данные в определенных строках меняли свое значение и имели строки до и после изменения шаблона данных. Например,
Таблица T1:
ID Operation Worker
1 1 A
2 1 A
3 2 A
4 2 A
5 1 A
6 1 A
7 1 A
Как получить идентификатор строк до и после изменения, например, идентификаторы 2,3,4,5?
Редактирование: у меня есть SQL Server 2008 R2, поэтому задержки, ведущие функции недоступны.
Всего 4 ответа
Вы можете попробовать это.
DECLARE @T1 TABLE(ID INT, Operation INT, Worker VARCHAR(5))
INSERT INTO @T1 VALUES
(1 ,1 ,'A'),
(2 ,1 ,'A'),
(3 ,2 ,'A'),
(4 ,2 ,'A'),
(5 ,1 ,'A'),
(6 ,1 ,'A'),
(7 ,1 ,'A')
;WITH T AS (
SELECT *, ROW_NUMBER()OVER(ORDER BY ID) AS RN FROM @T1)
SELECT T.ID
FROM T
LEFT JOIN T AS TP ON T.RN = TP.RN + 1
LEFT JOIN T AS TN ON T.RN = TN.RN - 1
WHERE
T.Operation <> TP.Operation
OR T.Operation <> TN.Operation
Результат:
ID
-----------
2
3
4
5
Это болезненно без lead()
и lag()
. Итак, давайте добавим их, используя cross apply
:
select t.*,
from t outer apply
(select top (1) tprev.*
from t tprev
where tprev.id < t.id
order by tprev.id desc
) tprev outer apply
(select top (1) tnext.*
from t tnext
where tnext.id > t.id
order by tnext.id asc
) tnext
where t.operation = 2 or
(tprev.operation = 2 or tprev.operation is null) or
(tnext.operation = 2 or tnext.operation is null)
Я думаю, вам нужно lead()
& lag()
:
select t.*
from (select t.*,
lag(operation, 1, operation) over (partition by worker order by id) as prev_op,
lead(operation, 1, operation) over (partition by worker order by id) as next_op
from tabke t
) t
where (operation <> prev_op) or (operation <> next_op);
EDIT: для более старой версии вы можете использовать apply
:
select t.*
from table t outer apply
( select top (1) prev_op.*
from table prev_op
where prev_op.id < t.id
order by prev_op.id desc
) prev_op outer apply
( select top (1) next_op.*
from table next_op
where next_op.id > t.id
order by prev_op.id asc
) next_op
where (t.operation <> prev_op.operation) or (t.operation <> next_op.operation);
Это можно сделать с NOT EXISTS
за исключением того, что вам нужно четыре :
SELECT *
FROM @t AS toselect
WHERE EXISTS (
SELECT 1
FROM @t AS prev
WHERE id < toselect.ID
AND Operation <> toselect.Operation
AND NOT EXISTS (
SELECT 1
FROM @t AS inbw
WHERE id < toselect.id
AND id > prev.id
)
) OR EXISTS (
SELECT 1
FROM @t AS prev
WHERE id > toselect.ID
AND Operation <> toselect.Operation
AND NOT EXISTS (
SELECT 1
FROM @t AS inbw
WHERE id > toselect.id
AND id < prev.id
)
)