如何得到某条记录的相邻5条记录？

[hwyqy]

假设当前记录是A，要得到A的前2条，后2条记录，共5条记录，本来也不难，问题是
如果A后面只有1条记录，那就是得到前3条，后1条记录，共5条，还涉及到如果总记录数不足5条之类
还请指教下，sql语句需要怎么写

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID INT IDENTITY(1,1),
 NAME VARCHAR(5))

INSERT INTO #T
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F' UNION ALL
SELECT 'G' UNION ALL
SELECT 'H' UNION ALL
SELECT 'I' 

GO

DECLARE @A INT

SET @A=8;

WITH CTE_1
AS
(SELECT *,'LESS' AS TYPE FROM #T WHERE ID BETWEEN @A-4 AND @A-1
UNION ALL
SELECT *,'MORE' AS TYPE FROM #T WHERE ID BETWEEN @A+1 AND @A+4),

CTE_2
AS
(SELECT *,
CASE WHEN TYPE='LESS' THEN 2*(ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY ID DESC))-1
ELSE 2*(ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY ID)) 
END AS SEQ
FROM CTE_1)

SELECT * 
FROM
(SELECT TOP 4 ID,NAME 
FROM CTE_2 
ORDER BY SEQ
UNION ALL
SELECT * FROM #T WHERE ID=@A) AS A
ORDER BY ID

[文盲老顾]

with t as (
	select 1 as id
	union all
	select 2
	union all
	select 3
	--union all
	--select 4
	--union all
	--select 5
	--union all
	--select 6
	--union all
	--select 8
	--union all
	--select 9
	union all
	select 10
),t1 as (
	select id,row_number() over(order by id) as rid from t
)
select c.* 
from t1 a
cross apply (select max(rid) as mid from t1) b
cross apply (
	select * 
	from t1 
	where rid between 
	(case when a.rid<=3 then 1 when b.mid>=a.rid+2 then a.rid-2 when b.mid>=5 then b.mid-4 else 1 end)
	and
	(case when a.rid>=b.mid-2 then b.mid when a.rid<3 then 5 when b.mid>=a.rid+2 then a.rid+2 else b.mid end)
) c
where a.id=1