DECLARE @ID INT SET @ID=5 SELECT TOP 6 * FROM TB WHERE ID<>@ID ORDER BY ABS(@ID-ID)
create proc kk @id int as begin declare qushu cursor for select * from tb where id=@id order by [time] open qushu
fetch relative -3 from qushu fetch relative 3 from qushu
close qushu deallocate qushu end
DECLARE @T TABLE(ID INT ) INSERT @T SELECT 1 INSERT @T SELECT 3 INSERT @T SELECT 5 INSERT @T SELECT 7 INSERT @T SELECT 10 INSERT @T SELECT 9 INSERT @T SELECT 12 INSERT @T SELECT 10 DECLARE @ID INT SET @ID=5 SELECT TOP 6 * FROM @T WHERE ID<>@ID ORDER BY ABS(@ID-ID) /*ID ----------- 7 3 1 9 10 10 */
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int,count int,time datetime) go insert into tb select 1,1,'2009-8-10' union all select 2,1,'2009-8-19' union all select 3,1,'2009-8-18' union all select 4,1,'2009-8-17' union all select 5,1,'2009-8-16' union all select 6,1,'2009-8-14' union all select 7,1,'2009-8-15' union all select 8,1,'2009-8-1' union all select 9,1,'2009-8-20' union all select 10,1,'2009-8-22' union all select 11,1,'2009-8-19' go alter proc kkkkkk @id int as begin declare @n1 int ,@n2 int set @n1=@id-4 set @n2=@id+5 declare qushu scroll cursor for select * from tb order by [time] open qushu fetch relative @n1 from qushu close qushu deallocate qushu end go exec kkkkkk 2/*-------------------*/
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int,count int,time datetime) go insert into tb select 1,1,'2009-8-10' union all select 2,1,'2009-8-19' union all select 3,1,'2009-8-18' union all select 4,1,'2009-8-17' union all select 5,1,'2009-8-16' union all select 6,1,'2009-8-14' union all select 7,1,'2009-8-15' union all select 8,1,'2009-8-1' union all select 9,1,'2009-8-20' union all select 10,1,'2009-8-22' union all select 11,1,'2009-8-19' go select * from tb order by time go DECLARE @ID INT SET @ID=5 SELECT TOP 6 * FROM tb t WHERE ID<>@ID ORDER BY ABS(DATEDIFF(dd,(select time from tb where id=t.id),(select time from tb where id=@id)))/*----------------- 8 1 2009-08-01 00:00:00.000 1 1 2009-08-10 00:00:00.000 6 1 2009-08-14 00:00:00.000 7 1 2009-08-15 00:00:00.000 5 1 2009-08-16 00:00:00.000 4 1 2009-08-17 00:00:00.000 3 1 2009-08-18 00:00:00.000 2 1 2009-08-19 00:00:00.000 11 1 2009-08-19 00:00:00.000 9 1 2009-08-20 00:00:00.000 10 1 2009-08-22 00:00:00.000 --下面是选出来的 4 1 2009-08-17 00:00:00.000 7 1 2009-08-15 00:00:00.000 6 1 2009-08-14 00:00:00.000 3 1 2009-08-18 00:00:00.000 2 1 2009-08-19 00:00:00.000 11 1 2009-08-19 00:00:00.000---------------*/
SET @ID=5
SELECT TOP 6 * FROM TB WHERE ID<>@ID ORDER BY ABS(@ID-ID)
@id int
as
begin
declare qushu cursor
for select * from tb where id=@id
order by [time]
open qushu
fetch relative -3 from qushu
fetch relative 3 from qushu
close qushu
deallocate qushu
end
INSERT @T SELECT 1
INSERT @T SELECT 3
INSERT @T SELECT 5
INSERT @T SELECT 7
INSERT @T SELECT 10
INSERT @T SELECT 9
INSERT @T SELECT 12
INSERT @T SELECT 10
DECLARE @ID INT
SET @ID=5
SELECT TOP 6 * FROM @T WHERE ID<>@ID ORDER BY ABS(@ID-ID)
/*ID
-----------
7
3
1
9
10
10
*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,count int,time datetime)
go
insert into tb
select 1,1,'2009-8-10' union all
select 2,1,'2009-8-19' union all
select 3,1,'2009-8-18' union all
select 4,1,'2009-8-17' union all
select 5,1,'2009-8-16' union all
select 6,1,'2009-8-14' union all
select 7,1,'2009-8-15' union all
select 8,1,'2009-8-1' union all
select 9,1,'2009-8-20' union all
select 10,1,'2009-8-22' union all
select 11,1,'2009-8-19'
go
alter proc kkkkkk
@id int
as
begin
declare @n1 int ,@n2 int
set @n1=@id-4
set @n2=@id+5
declare qushu scroll cursor
for select * from tb
order by [time]
open qushu
fetch relative @n1 from qushu
close qushu
deallocate qushu
end
go
exec kkkkkk 2/*-------------------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,count int,time datetime)
go
insert into tb
select 1,1,'2009-8-10' union all
select 2,1,'2009-8-19' union all
select 3,1,'2009-8-18' union all
select 4,1,'2009-8-17' union all
select 5,1,'2009-8-16' union all
select 6,1,'2009-8-14' union all
select 7,1,'2009-8-15' union all
select 8,1,'2009-8-1' union all
select 9,1,'2009-8-20' union all
select 10,1,'2009-8-22' union all
select 11,1,'2009-8-19'
go
select * from tb order by time
go
DECLARE @ID INT
SET @ID=5
SELECT TOP 6 * FROM tb t WHERE ID<>@ID ORDER BY ABS(DATEDIFF(dd,(select time from tb where id=t.id),(select time from tb where id=@id)))/*-----------------
8 1 2009-08-01 00:00:00.000
1 1 2009-08-10 00:00:00.000
6 1 2009-08-14 00:00:00.000
7 1 2009-08-15 00:00:00.000
5 1 2009-08-16 00:00:00.000
4 1 2009-08-17 00:00:00.000
3 1 2009-08-18 00:00:00.000
2 1 2009-08-19 00:00:00.000
11 1 2009-08-19 00:00:00.000
9 1 2009-08-20 00:00:00.000
10 1 2009-08-22 00:00:00.000
--下面是选出来的
4 1 2009-08-17 00:00:00.000
7 1 2009-08-15 00:00:00.000
6 1 2009-08-14 00:00:00.000
3 1 2009-08-18 00:00:00.000
2 1 2009-08-19 00:00:00.000
11 1 2009-08-19 00:00:00.000---------------*/