Как получить отчеты до и после получения?
У меня есть следующая структура таблицы:
Id, Message
1, John Doe
2, Jane Smith
3, Error
4, Jane Smith
Есть ли способ получить запись об ошибках и окружающие записи? то есть найти все Ошибки и запись до и после них.
Ответы
Ответ 1
;WITH numberedlogtable AS
(
SELECT Id,Message,
ROW_NUMBER() OVER (ORDER BY ID) AS RN
FROM logtable
)
SELECT Id,Message
FROM numberedlogtable
WHERE RN IN (SELECT RN+i
FROM numberedlogtable
CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
WHERE Message='Error')
Ответ 2
WITH err AS
(
SELECT TOP 1 *
FROM log
WHERE message = 'Error'
ORDER BY
id
),
p AS
(
SELECT TOP 1 l.*
FROM log
WHERE id <
(
SELECT id
FROM err
)
ORDER BY
id DESC
)
SELECT TOP 3 *
FROM log
WHERE id >
(
SELECT id
FROM p
)
ORDER BY
id
Ответ 3
Адаптируйте эту процедуру, чтобы выбрать вашу цель.
DECLARE @TargetId int
SET @TargetId = 3
select *
from LogTable
where Id in (-- "before"
select max(Id)
from LogTable
where Id < @TargetId
-- target
union all select @TargetId
-- "after"
union all select min(Id)
from LogTable
where Id > @TargetId)
Ответ 4
;WITH Logs AS
(
SELECT ROW_NUMBER() OVER (ORDER BY id), id, message as rownum FROM LogTable lt
)
SELECT curr.id, prev.id, next.id
FROM Logs curr
LEFT OUTER JOIN Logs prev ON curr.rownum+1=prev.rownum
RIGHT OUTER JOIN Logs next ON curr.rownum-1=next.rownum
WHERE curr.message = 'Error'
Ответ 5
select id,messag from
(Select (Row_Number() over (order by ID)) as RNO, * from #Temp) as A,
(select SubRNO-1 as A,
SubRNO as B,
SubRNO+1 as C
from (Select (Row_Number() over (order by ID)) as SubRNO, * from #Temp) as C
where messag = 'Error') as B
where A.RNO = B.A or A.RNO = B.B or A.RNO = B.C
Ответ 6
select id, message from tbl where id in (
select id from tbl where message = "error"
union
select id-1 from tbl where message = "error"
union
select id+1 from tbl where message = "error"
)