在一张表中,有name 跟title 2个列
知道name列中的一个值为111
想要查找出下一项的name值,具体怎么实现?
如果111为name值中的第一项,那上一项即name值中的最后一项如何查找?
想在asp.net中实现
知道name列中的一个值为111
想要查找出下一项的name值,具体怎么实现?
如果111为name值中的第一项,那上一项即name值中的最后一项如何查找?
想在asp.net中实现
调试欢乐多
就是给记录添加一个行号select rn,name,title
from
(select *,rn=row_number() over(order by getdate()) from tb)t
where name='xxx'
根据name='xxx'找出的rn值就是行号
(
[name] VARCHAR(10),
title VARCHAR(10)
)
INSERT #temp
select 'aa', 'a11' union all
select 'aa', 'a22' union all
select 'aa', 'a33' union all
select 'aa', 'a44' union all
select 'bb', 'a55' union all
select 'bb', 'a66' union all
select 'bb', 'a77' union all
select 'bb', 'a88' union all
select 'cc', 'a99' union all
select 'cc', 'a10' union all
select 'cc', 'a11'
GO
--SQL:
;WITH cte AS
(
SELECT rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()), * FROM #temp
)
SELECT
a.*,
[rn-1] = CASE WHEN b.name IS NULL THEN (SELECT TOP(1) [name] FROM cte ORDER BY rowno desc) ELSE b.name end,
[rn+1] = CASE WHEN c.name IS NULL THEN (SELECT TOP(1) [name] FROM cte WHERE rowno=1) ELSE c.name end
FROM cte a
LEFT JOIN cte b
ON a.rowno = b.rowno + 1
LEFT JOIN cte c
ON a.rowno = c.rowno - 1
/*
rowno name title rn-1 rn+1
1 aa a11 cc aa
2 aa a22 aa aa
3 aa a33 aa aa
4 aa a44 aa bb
5 bb a55 aa bb
6 bb a66 bb bb
7 bb a77 bb bb
8 bb a88 bb cc
9 cc a99 bb cc
10 cc a10 cc cc
11 cc a11 cc aa
*/