if object_id('[tb]') is not null drop table [tb] go create table [tb]([类别] varchar(3),[编号] int) insert [tb] select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5select top 1 a.* from tb a, tb b where cast(a.类别 as int)=cast(b.类别 as int)+1 and b.编号=7 order by a.编号--测试结果: /* 类别 编号 ---- ----------- 002 4 */
create table #tb([Type] varchar(3),Num int) insert #tb select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5select * from #tb order by [Type],Num 不是很懂Lz的意思
--测试数据 declare @t table(类别 varchar(3), 编号 int) insert @t select '001',1 union select '001',2 union select '001',3 union select '001',6 union select '001',7 union select '002',4 union select '002',5 declare @编号 int set @编号=7--查询 select top 1 * from @t where 类别>(select 类别 from @t where 编号=@编号) or (类别=(select 类别 from @t where 编号=@编号) and 编号>@编号) /* 类别 编号 ---- ----------- 002 4 */
if object_id('[tb]') is not null drop table [tb] go create table [tb]([类别] varchar(3),[编号] int) insert [tb] select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5 IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE # GO SELECT *,ID=IDENTITY(INT,1,1)INTO # FROM TB ORDER BY [类别] ,[编号] DECLARE @编号 INT SET @编号=3 SELECT 类别 , 编号 FROM # WHERE ID IN(SELECT ID+1 FROM # WHERE [编号]=@编号 ) SET @编号=7 SELECT 类别, 编号 FROM # WHERE ID IN(SELECT ID+1 FROM # WHERE [编号]=@编号 ) /*类别 编号 ---- ----------- 001 6类别 编号 ---- ----------- 002 4 */
把where 后的classid都换成right(classid,3)
if object_id('[tb]') is not null drop table [tb] go create table [tb](lei varchar(3),id int) insert [tb] select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5 --select * from tb declare @a int,@b varchar(10),@i int set @a=7 --输入的参数值 select @b=lei from tb where id=@a set @i=(select count(*) from tb where lei=@b and id>@a) if @i <>0 select @a=(select top 1 id from tb where lei=@b and id>@a) else begin select @b=(select top 1 lei from tb where lei>@b) select @a=(select min(id) from tb where lei=@b) end select @a drop table tb /* ----------- 4(1 行受影响) */
把你部分数据和SQL语句贴出来看看
if object_id('[tb]') is not null drop table [tb] go create table [tb](lei varchar(3),id int) insert [tb] select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=2 set @lei_tmp='001' if (select max(id) from tb where lei=@lei_tmp group by lei)>@id_tmp select top 1 * from tb where id>@id_tmp order by lei,id else begin select top 1 * from tb where lei>@lei_tmp order by lei,id end
if object_id('[tb]') is not null drop table [tb] go create table [tb](lei varchar(3),id int) insert [tb] select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=2 set @lei_tmp='001' if (select max(id) from tb where lei=@lei_tmp group by lei)=@id_tmp select top 1 * from tb where lei>@lei_tmp order by lei,id else begin select top 1 * from tb where id>@id_tmp and lei =@lei_tmp order by lei,id end/* 测试数据 结果 --------------- 001 2 001 3 001 7 002 4 002 5 null 002 4 002 5 */
declare @T table([Type] varchar(3),ID int) insert into @T select '001','1' union all select '002','2' union all select '003','3' union all select '001','6' union all select '001','7' union all select '001','4' union all select '001','5' select eid=identity(int,1,1),* into #T from @T
select top 1 * from #T where eid> ( select top 1 eid from #T where id=2 ) drop table #T
if object_id('[tb]') is not null drop table [tb] go /* 1 001001 2 001001 3 001001 4 002001 5 002001 6 002001 7 001001 8 001001 9 002001 10 001002 11 001002 */create table [tb](id int,lei varchar(3)) insert [tb] select 1,'001' union all select 2,'001'union all select 3,'001'union all select 4,'002'union all select 6,'002'union all select 5,'002'union all select 7,'001'declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=1 set @lei_tmp='001' --select top 10000 * from tb order by lei,id if (select max(id) from tb where lei=@lei_tmp group by lei)=@id_tmp select top 1 * from (select top 10000 * from tb order by lei,id)a where lei>@lei_tmp else begin select top 1 * from (select top 10000 * from tb order by lei,id)a where id>@id_tmp and lei=@lei_tmp end
SELECT ID=IDENTITY(INT,1,1),* INTO # FROM tbSELECT TOP 1 类别, 编号 FROM # WHERE ID>(SELECT ID FROM # WHERE 编号=7) ORDER BY ID
declare @table table (m_type varchar(3),m_no int) insert into @table select '001',1 union all select '001',2 union all select '001',3 union all select '001',6 union all select '001',7 union all select '002',4 union all select '002',5select row_number()over(order by m_type, m_no) as rownum, * from @table /* rownum m_type m_no -------------------- ------ ----------- 1 001 1 2 001 2 3 001 3 4 001 6 5 001 7 6 002 4 7 002 5 */
类别,
编号
FROM #
WHERE ID>(SELECT ID
FROM #
WHERE 编号=7)
ORDER BY ID
------------------------------------------------
你是怎么定位在编号7的?
go
create table [tb]([类别] varchar(3),[编号] int)
insert [tb]
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5select top 1 a.*
from tb a, tb b
where cast(a.类别 as int)=cast(b.类别 as int)+1
and b.编号=7
order by a.编号--测试结果:
/*
类别 编号
---- -----------
002 4
*/
insert #tb
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5select * from #tb order by [Type],Num
不是很懂Lz的意思
declare @t table(类别 varchar(3), 编号 int)
insert @t
select '001',1 union
select '001',2 union
select '001',3 union
select '001',6 union
select '001',7 union
select '002',4 union
select '002',5
declare @编号 int
set @编号=7--查询
select top 1 * from @t where 类别>(select 类别 from @t where 编号=@编号)
or (类别=(select 类别 from @t where 编号=@编号) and 编号>@编号)
/*
类别 编号
---- -----------
002 4
*/
id classid
1 001
2 001
3 001
4 002
5 002
6 002
7 001
8 001
9 002
如果当前是6的话,下一条应该是9,但是你的代码查出来的是7
id classid
1 001001
2 001001
3 001001
4 002001
5 002001
6 002001
7 001001
8 001001
9 002001
10 001002
11 001002
因为这里classid(类别号)是存在子项的,比如001001001,002001001等等
go
create table [tb]([类别] varchar(3),[编号] int)
insert [tb]
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5
IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE #
GO
SELECT *,ID=IDENTITY(INT,1,1)INTO # FROM TB ORDER BY [类别] ,[编号]
DECLARE @编号 INT
SET @编号=3
SELECT 类别 , 编号 FROM # WHERE ID IN(SELECT ID+1 FROM # WHERE [编号]=@编号 )
SET @编号=7
SELECT 类别, 编号 FROM # WHERE ID IN(SELECT ID+1 FROM # WHERE [编号]=@编号 )
/*类别 编号
---- -----------
001 6类别 编号
---- -----------
002 4
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](lei varchar(3),id int)
insert [tb]
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5
--select * from tb
declare @a int,@b varchar(10),@i int
set @a=7 --输入的参数值
select @b=lei from tb where id=@a
set @i=(select count(*) from tb where lei=@b and id>@a)
if @i <>0
select @a=(select top 1 id from tb where lei=@b and id>@a)
else
begin
select @b=(select top 1 lei from tb where lei>@b)
select @a=(select min(id) from tb where lei=@b)
end
select @a
drop table tb
/*
-----------
4(1 行受影响)
*/
go
create table [tb](lei varchar(3),id int)
insert [tb]
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=2
set @lei_tmp='001'
if (select max(id) from tb where lei=@lei_tmp group by lei)>@id_tmp
select top 1 * from tb where id>@id_tmp order by lei,id
else
begin
select top 1 * from tb where lei>@lei_tmp order by lei,id
end
go
create table [tb](lei varchar(3),id int)
insert [tb]
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=2
set @lei_tmp='001'
if (select max(id) from tb where lei=@lei_tmp group by lei)=@id_tmp
select top 1 * from tb where lei>@lei_tmp order by lei,id
else
begin
select top 1 * from tb where id>@id_tmp and lei =@lei_tmp order by lei,id
end/*
测试数据 结果
---------------
001 2 001 3
001 7 002 4
002 5 null
002 4 002 5
*/
declare @T table([Type] varchar(3),ID int)
insert into @T
select '001','1' union all
select '002','2' union all
select '003','3' union all
select '001','6' union all
select '001','7' union all
select '001','4' union all
select '001','5' select eid=identity(int,1,1),* into #T from @T
select top 1 * from #T
where eid>
(
select top 1 eid from #T where id=2
)
drop table #T
go
/*
1 001001
2 001001
3 001001
4 002001
5 002001
6 002001
7 001001
8 001001
9 002001
10 001002
11 001002
*/create table [tb](id int,lei varchar(3))
insert [tb]
select 1,'001' union all
select 2,'001'union all
select 3,'001'union all
select 4,'002'union all
select 6,'002'union all
select 5,'002'union all
select 7,'001'declare @id_tmp int,@lei_tmp varchar(3)set @id_tmp=1
set @lei_tmp='001'
--select top 10000 * from tb order by lei,id
if (select max(id) from tb where lei=@lei_tmp group by lei)=@id_tmp
select top 1 * from (select top 10000 * from tb order by lei,id)a where lei>@lei_tmp
else
begin
select top 1 * from (select top 10000 * from tb order by lei,id)a where id>@id_tmp and lei=@lei_tmp
end
类别,
编号
FROM #
WHERE ID>(SELECT ID
FROM #
WHERE 编号=7)
ORDER BY ID
declare @table table (m_type varchar(3),m_no int)
insert into @table
select '001',1 union all
select '001',2 union all
select '001',3 union all
select '001',6 union all
select '001',7 union all
select '002',4 union all
select '002',5select row_number()over(order by m_type, m_no) as rownum, * from @table
/*
rownum m_type m_no
-------------------- ------ -----------
1 001 1
2 001 2
3 001 3
4 001 6
5 001 7
6 002 4
7 002 5
*/