获取某条记录的上下两条

[pdsnet]

select   *   from   WS_Video where   ABS(V_ID-@V_ID) <3  and   V_ID <> @V_ID   and v_id<>@V_ID  and V_type=@type order   by   V_ID asc

这个写法不行 如果 [V_ID] 不连续就挂了..

CREATE TABLE [WS_Video] (
[V_ID] [int] IDENTITY (1, 1) NOT NULL ,
[V_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_path] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_picpath] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[V_content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_click] [int] NOT NULL CONSTRAINT [DF_WS_Video_V_click] DEFAULT (0),
[V_type] [tinyint] NOT NULL CONSTRAINT [DF_WS_Video_V_type] DEFAULT (0),

[V_time] [datetime] NOT NULL CONSTRAINT [DF_WS_Video_V_time] DEFAULT (getdate()),
CONSTRAINT [PK_WS_Video] PRIMARY KEY CLUSTERED
(
[V_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


根据V_time 排序
1
2
3
5
6
7
8
9
10
11
12

id是5 的话 返回 2,3, 6,7 , 是10的话 返回 8,9,11,12
谢谢

[pdsnet]

select * from WS_Video where ABS(V_ID-@V_ID) <3 and V_ID <> @V_ID and v_id<>@V_ID and V_type=@type order by V_ID asc

这样看着方便 .

[guguda2008]

WHERE ABS(ID-5)<3

[水族杰纶]

set nocount on
declare @t table(ID int )
insert @t select 1 
insert @t select 2 
insert @t select 3 
insert @t select 5 
insert @t select 6 
insert @t select 7 
insert @t select 8 
insert @t select 9 
insert @t select 10 
insert @t select 11 
insert @t select 12 
declare @id int 
set @id=5
select top 4 * from @t where id!=@id order by abs(@id-id) 
set @id=10
select top 4 * from @t where id!=@id order by abs(@id-id) 
/*ID
-----------
6
7
3
2

ID
-----------
9
11
8
12*/

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb( a int )
go
insert tb SELECT 
1 UNION ALL SELECT 
2 UNION ALL SELECT 
3 UNION ALL SELECT 
5 UNION ALL SELECT 
6 UNION ALL SELECT 
7 UNION ALL SELECT 
8 UNION ALL SELECT 
9 UNION ALL SELECT 
10 UNION ALL SELECT 
11 UNION ALL SELECT 
12 
go
select identity(int,1,1) as id, A into # from tb
declare @n int
set @n=6
select A
from # 
where ID between (select ID from # where A=@n )-2 and (select ID from # where A=@n )+2
  and A<>@n
go
/*------------
A
-----------
3
5
7
8

-------*/

[guguda2008]

[Quote=引用 3 楼 wufeng4552 的回复:]
SQL codeset nocountondeclare@ttable(IDint )insert@tselect1insert@tselect2insert@tselect3insert@tselect5insert@tselect6insert@tselect7insert@tselect8insert@tselect9insert@tselect10insert@tselect11insert@tselect12declare@idintset@id=5selecttop4*from@twhere id!=@idorderbyabs(@id-id)set@id=10selecttop4*from@twhere id!=@idorderbyabs(@id-id)/*ID
-----------
6
7
3
2

ID
-----------
9
11
8
12*/
[/Quote]

学习，是不是用TOP + ORDER比用WHERE 快啊？

[w_lion]

3楼的问题

如果是

set nocount on
declare @t table(ID int )
insert @t select 1
insert @t select 2
insert @t select 5
insert @t select 6
insert @t select 7
insert @t select 8
insert @t select 9
insert @t select 10
insert @t select 11
insert @t select 12
declare @id int
set @id=5
select top 4 * from @t where id!=@id order by abs(@id-id)

结果为
6
7
8
2

不知道和楼主要的有没有差异

[zhaoyh0530]

select identity(int,1,1) as id, A into # from tb
declare @n int
set @n=5
select A
from # 
where  A<>@n and ID between (select ID from # where A=@n )-2 and (select ID from # where A=@n )+2 

[csdyyr]

DECLARE @TB TABLE([COL] INT)
INSERT @TB 
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 6 UNION ALL 
SELECT 7 UNION ALL 
SELECT 8 UNION ALL 
SELECT 9 UNION ALL 
SELECT 10 UNION ALL 
SELECT 11 UNION ALL 
SELECT 12

SELECT *
FROM @TB AS T
WHERE EXISTS(SELECT 1 FROM @TB WHERE COL>=T.COL AND COL<5 HAVING COUNT(*) BETWEEN 1 AND 2)
         OR EXISTS(SELECT 1 FROM @TB WHERE COL>5 AND COL<=T.COL HAVING COUNT(*) BETWEEN 1 AND 2)

/*
COL         
----------- 
2
3
6
7
*/

[pdsnet]

谢谢以上各位

[htl258_Tony]

set nocount on 
declare @t table(ID int ) 
insert @t select 1 
insert @t select 2 
insert @t select 5 
insert @t select 6 
insert @t select 7 
insert @t select 8 
insert @t select 9 
insert @t select 10 
insert @t select 11 
insert @t select 12 
declare @id int 
set @id=5 
select * from (select top 2 * from @t where id<@id order by id desc) t
union all
select * from (select top 2 * from @t where id>@id order by id) t
/*
ID
-----------
2
1
6
7
*/
set @id=10 
select * from (select top 2 * from @t where id<@id order by id desc) t
union all
select * from (select top 2 * from @t where id>@id order by id) t
/*
ID
-----------
9
8
11
12
*/

[w_lion]

应该解决好了

[xiequan2]

[Quote=引用楼主 pdsnet 的回复:]
SQL codeselect*from WS_VideowhereABS(V_ID-@V_ID)<3and V_ID<>@V_IDand v_id<>@V_IDand V_type=@typeorderby V_IDasc  这个写法不行 如果  [V_ID] 不连续就挂了..

CREATE TABLE [WS_Video] (
[V_ID] [int] IDENTITY (1, 1) NOT NULL ,
[V_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_path] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_picpath] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[V_content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[V_click] [int] NOT NULL CONSTRAINT [DF_WS_Video_V_click] DEFAULT (0),
[V_type] [tinyint] NOT NULL CONSTRAINT [DF_WS_Video_V_type] DEFAULT (0),

[V_time] [datetime] NOT NULL CONSTRAINT [DF_WS_Video_V_time] DEFAULT (getdate()),
CONSTRAINT [PK_WS_Video] PRIMARY KEY  CLUSTERED
(
[V_ID]
)  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


根据V_time 排序
1
2
3
5
6
7
8
9
10
11
12

id是5 的话 返回 2,3, 6,7 , 是10的话 返回 8,9,11,12
谢谢
[/Quote]

declare @t table(ID int )
insert @t select 1 
insert @t select 2 
insert @t select 3 
insert @t select 5 
insert @t select 6 
insert @t select 7 
insert @t select 8 
insert @t select 9 
insert @t select 10 
insert @t select 11 
insert @t select 12 
declare @id int 
set @id=10
;
with cte1 as
(
	select *,flag=row_number() over (order by id) from @t
)
select id from cte1 
where flag between (select flag from cte1 where id=@id)-2 and (select flag from cte1 where id=@id)+2 and id<>@id

 /*
id
-----------
8
9
11
12
*/

[mousefog]

up

[pdsnet]

正在测试

[pdsnet]

都没根据时间排序 .我自己试下

[pdsnet]

2,3 楼是不行的 . 非常感谢各位的帮忙