假設當前記錄是A，要得到A的前2條，后2條記錄，共5條記錄，本來也不難，問題是
如果A后面只有1條記錄，那就是得到前3條，后1條記錄，共5條，還涉及到如果總記錄數不足5條之類
還請指教下，sql陳述句需要怎么寫
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/149498.html
標籤：基礎類
上一篇：SQL列約束問題
下一篇：子查詢回傳的值不止一個