Получить строки из таблицы sql на основе определенного значения
Я создал следующую таблицу:
USE [myTestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [numeric](19, 0) NOT NULL,
[action] [nvarchar](255) NULL,
[masterid] [numeric](19, 0) NULL
) ON [PRIMARY]
GO
Данные в таблице:
id action masterid
1906035 001 514
1906057 002 514
1906064 002 514
1906956 003 514
1907007 002 514
1907010 004 514
1907097 002 514
Все строки имеют один и тот же masterid, и я хочу вернуть только те строки, значение действия которых равно 002, и они размещаются сразу после действия 001 и перед любыми другими действиями.
Строки со значением 002 после любого другого действия (например, 003, 004) не должны возвращаться.
Чего я хочу добиться, так это:
id action masterid
1906057 002 514
1906064 002 514
Используя следующий запрос, возвращает все строки с действием 002:
select t.[id]
,t.[action]
,t.[masterid]
from [myTestDB].[dbo].[test] t
left join [myTestDB].[dbo].[test] t2 on (t2.masterid = t.masterid and t2.action = 001)
where t.action = 002
and t.id > t2.id
id action masterid
1906057 002 514
1906064 002 514
1907007 002 514
1907097 002 514
Как исключить строки с идентификаторами 1907007 и 1907097, которые появляются после действий 003 и 004?
Заранее спасибо!
Ответы 1
Я бы назвал это проблемой пробелов и островов. Во-первых, определите группы записей, которые сбрасываются каждый раз при появлении действия «001». Затем подсчитайте количество записей, отличных от «002», с начала каждой группы и, наконец, отфильтруйте:
select id, action, masterid
from (
select t.*,
sum(case when action in ('001', '002') then 0 else 1 end) over(partition by masterid, grp order by id) as flag
from (
select t.*,
sum(case when action = '001' then 1 else 0 end) over(partition by masterid order by id) as grp
from test t
) t
) t
where action = '002' and flag = 0 and grp > 0
id | action | masterid
------: | :----- | -------:
1906057 | 002 | 514
1906064 | 002 | 514
@praman: извините, я неправильно понял ваш вопрос. Я исправил ответ и добавил скрипку.
Большое спасибо ГМБ!
Что произойдет, если действие 001 не существует? Если я удалю строку с этим значением (001) и выполню ваш последний запрос, то все равно получу тот же результат. Я не должен получить никаких результатов, если 001 не существует, так как я хочу получить строки после 001. Есть идеи? Еще раз спасибо за ваше время.
@praman: да, это хороший момент. Мы можем просто добавить еще одно условие в предложение where, чтобы справиться с этим. Обновлено в ответе.
Спасибо ГМБ. Однако оба эти запроса возвращают только одну строку (id 1906057). Мне также нужна строка с идентификатором 1906064.