问个查询的问题???这样的能查询吗

[2004v2004]

一个表table4
有四个字段分别是 :A,B,C,D这四个

A B C D
1 3 5 8
5 6 7 8
1 2 3 4
2 9 8 7
3 7 6 5
4 8 7 6
8 9 0 4
5 4 3 2
1:然后输入4个数字 例如输入 5,1,2,3

这样就在 A 列开始查询,别的列不管 按照输入的4个数字顺序查找 连续挨这的这个数据的下一条是什么 例如这个就是

4 8 7 6

如果输入的是 1,2,3,4
查询到的应该是 8 9 0 4
如果输入3,4,8,5 查询到的是 没有

该怎么写这样的语句.

2:这个是顺序查 还可以这样反这查询

例如查询的是3,4,8,5 得到的结果 2 9 8 7

不知道我说明白了吗

[dgsunrise]

没看明白，连续挨这的这个数据的下一条是什么 ？

[dawugui]

create table tb(A int,B int,C int,D int)
insert into tb values(1,       3,       5,       8 )
insert into tb values(5,       6,       7,       8 )
insert into tb values(1,       2,       3,       4 )
insert into tb values(2,       9,       8,       7 )
insert into tb values(3,       7,       6,       5 )
insert into tb values(4,       8,       7,       6 ) 
insert into tb values(8,       9,       0,       4 )
insert into tb values(5,       4,       3,       2 )
go
declare @n1 as int
declare @n2 as int
declare @n3 as int
declare @n4 as int
set @n1 = 5
set @n2 = 1
set @n3 = 2
set @n4 = 3

select id=identity(int,1,1), * into tmp from tb

select a,b,c,d from tmp where id in
(
  select d.id + 1 from tmp a,tmp b,tmp c ,tmp d
  where a.a = @n1 and
      a.id = b.id - 1 and b.a = @n2 and
      a.id = c.id - 2 and c.a = @n3 and 
      a.id = d.id - 3 and d.a = @n4 
)

drop table tb,tmp
/*
a           b           c           d           
----------- ----------- ----------- ----------- 
4           8           7           6

（所影响的行数为 1 行）
*/

[dawugui]

create table tb(A int,B int,C int,D int)
insert into tb values(1,       3,       5,       8 )
insert into tb values(5,       6,       7,       8 )
insert into tb values(1,       2,       3,       4 )
insert into tb values(2,       9,       8,       7 )
insert into tb values(3,       7,       6,       5 )
insert into tb values(4,       8,       7,       6 ) 
insert into tb values(8,       9,       0,       4 )
insert into tb values(5,       4,       3,       2 )
go

select id=identity(int,1,1), * into tmp from tb

--顺序查
declare @n1 as int
declare @n2 as int
declare @n3 as int
declare @n4 as int
set @n1 = 5
set @n2 = 1
set @n3 = 2
set @n4 = 3
select a,b,c,d from tmp where id in
(
  select d.id + 1 from tmp a,tmp b,tmp c ,tmp d
  where a.a = @n1 and
      a.id = b.id - 1 and b.a = @n2 and
      a.id = c.id - 2 and c.a = @n3 and 
      a.id = d.id - 3 and d.a = @n4 
)
/*
a           b           c           d           
----------- ----------- ----------- ----------- 
4           8           7           6

（所影响的行数为 1 行）
*/

--反序查
declare @t1 as int
declare @t2 as int
declare @t3 as int
declare @t4 as int
set @t1 = 3
set @t2 = 4
set @t3 = 8
set @t4 = 5
select a,b,c,d from tmp where id in
(
  select a.id - 1 from tmp a,tmp b,tmp c ,tmp d
  where a.a = @t1 and
      a.id = b.id - 1 and b.a = @t2 and
      a.id = c.id - 2 and c.a = @t3 and 
      a.id = d.id - 3 and d.a = @t4 
)
/*
a           b           c           d           
----------- ----------- ----------- ----------- 
2           9           8           7

（所影响的行数为 1 行）
*/
drop table tb,tmp

[Limpire]

--原始数据：@4
declare @4 table(A int,B int,C int,D int)
insert @4
select 1,3,5,8 union all
select 5,6,7,8 union all
select 1,2,3,4 union all
select 2,9,8,7 union all
select 3,7,6,5 union all
select 4,8,7,6 union all
select 8,9,0,4 union all
select 5,4,3,2

--必须有唯一标识数据的字段如标识列
declare @T table(id int identity,A int,B int,C int,D int)
insert @T select * from @4

--正序：5,1,2,3
select A,B,C,D from @T a
where exists (select 1 from @T where id=a.id-4 and A=5)
	and exists (select 1 from @T where id=a.id-3 and A=1)
	and exists (select 1 from @T where id=a.id-2 and A=2)
	and exists (select 1 from @T where id=a.id-1 and A=3)
/*
A           B           C           D           
----------- ----------- ----------- ----------- 
4           8           7           6
*/

--逆序：3,4,8,5
select A,B,C,D from @T a
where exists (select 1 from @T where id=a.id+1 and A=3)
	and exists (select 1 from @T where id=a.id+2 and A=4)
	and exists (select 1 from @T where id=a.id+3 and A=8)
	and exists (select 1 from @T where id=a.id+4 and A=5)
/*
A           B           C           D           
----------- ----------- ----------- ----------- 
2           9           8           7
*/

[2004v2004]

楼上的两位 你俩的id 连续的可以 ，不过不连续呢 就不好使了啊

[迷失的空间]

我觉得你的库设计的有问题，最好改一下数据库的设计。一个好的设计能够让人很简单的对数据进行查询。

[dawugui]

小楼已经说了,ID不联系做不了.

你必须将原始数据生成一个ID连续的表才行.

[2004v2004]

我知道了 ，不是我的库有问题 ，人家就要这样的数据 ，我使用个临时表就ok 了。谢谢了 没有看到你也用个临时表 ，结了