If only 1 behind A record, it's got before 3, after 1 records, A total of 5, less than 5 article also involves if the total number of records such as
Also, please advise, how to write SQL statements need
CodePudding user response:
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
The select 10
), t1 as (
Select id, row_number () over (order by id) as rids the from t
)
The select c. *
The from t1 a
Cross the apply (select Max (rid) as mid from t1) b
Cross the apply (
Select *
The from t1
Where rids between
(case when a.r id<1=3 then the when b.m id> - 2 + 2=a.r id then a.r id when b.m id> 1=5 then b.m id - 4 else end)
And
(case when a.r id>=b.m id - 2 then b.m id when a.r id<3 then 5 when b.m id> + 2=a.r id then a.r id + 2 else b.m id end)
C)
Where Anderson, d=1
CodePudding user response:
IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL
DROP TABLE # T
GO
The CREATE TABLE # T
(ID INT IDENTITY (1, 1),
The NAME VARCHAR (5))
INSERT INTO # T
SELECT the 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT the 'C' UNION ALL
SELECT the 'D' UNION ALL
SELECT the 'E' UNION ALL
SELECT the 'F' UNION ALL
SELECT the 'G' UNION ALL
SELECT the 'H' UNION ALL
SELECT the 'I'
GO
DECLARE @ A INT
The SET @ A=8;
WITH CTE_1
AS
(SELECT *, 'LESS' AS the TYPE FROM the WHERE ID # T BETWEEN @ A - 4 AND @ A - 1
UNION ALL
SELECT *, 'MORE' AS the TYPE FROM the WHERE ID # T BETWEEN @ A + 1 AND @ A + 4),
CTE_2
AS
(SELECT *,
CASE the WHEN TYPE='LESS THEN 2 * (ROW_NUMBER () OVER (PARTITION BY TYPE ORDER BY ID DESC)) - 1
The ELSE 2 * (ROW_NUMBER () OVER (PARTITION BY TYPE ORDER BY ID))
END AS SEQ
The FROM CTE_1)
SELECT *
The FROM
(SELECT the TOP 4 ID, NAME
The FROM CTE_2
The ORDER BY SEQ
UNION ALL
SELECT * FROM the WHERE ID=@ # T A) AS A
The ORDER BY ID