简单问题 高手指教
有如下表
id group name
1 2 ss
2 3 ds
3 3 er
4 2 sd
5 3 sd
我想得到同一个组的上一个id号，id是递增的
比如我知道 id 5 我怎么得到他的上一个id3呢？
[解决办法]
select top 1 id from 表 where [group]=(select [group] from 表 where id=5) and id < 5 order by id desc
[解决办法]
select
t.*
from
表 t
where
t.id <5
and
not exists(select 1 from 表 where and id <5 and group=t.group and id> t.id)
[解决办法]
--测试数据：@Test
declare @Test table(id int,[group] int,name varchar(2))
insert @Test
select 1,2, 'ss ' union all
select 2,3, 'ds ' union all
select 3,3, 'er ' union all
select 4,2, 'sd ' union all
select 5,3, 'sd '
declare @id int
set @id=5
select top 1 * from @Test a where [group]=(select [group] from @Test where id=@id) and id <@id order by id desc
[解决办法]
create table tb (id int,[group] int,name varchar(10))
insert into tb values(1, 2, 'ss ')
insert into tb values(2, 3, 'ds ')
insert into tb values(3, 3, 'er ')
insert into tb values(4, 2, 'sd ')
insert into tb values(5, 3, 'sd ')
go
declare @id as int
set @id = 5
select max(id) id from
(select name , max(id) id from tb group by name) t
where id <> @id
drop table tb
/*
id
-----------
3
（所影响的行数为 1 行）
*/
[解决办法]
declare @a table(id int identity(1,1),groups int,name varchar(20))
insert @a
select 2, 'ss '
union all
select 3, 'ds '
union all
select 3, 'er '
union all
select 2, 'sd '
union all
select 3, 'sd '
select top 1 * from @a where groups=(select groups from @a where id=5 ) order by id desc
/*
（所影响的行数为 5 行）
id groups name
----------- ----------- --------------------
5 3 sd
（所影响的行数为 1 行）
*/
[解决办法]
都是高手，学习
[解决办法]
少了个条件 id <5
[解决办法]
declare @a table(id int identity(1,1),groups int,name varchar(20))
insert @a
select 2, 'ss '
union all
select 3, 'ds '
union all
select 3, 'er '
union all
select 2, 'sd '
union all
select 3, 'sd '
select top 1 * from @a where groups=(select groups from @a where id=5 ) and id <5 order by id desc
/*
（所影响的行数为 5 行）
id groups name
----------- ----------- --------------------
3 3 er
（所影响的行数为 1 行）
*/
[解决办法]
select top 1 * from tb where [group]=(select [group] from tb where id=5) and id <> 5 order by id desc
[解决办法]
create table testA (id int, [group] int, [name] nvarchar(20))
insert testA
select 1,2, 'ss '
union all select 2,3, 'ds '
union all select 3,3, 'er '
union all select 4,2, 'sd '
union all select 5,3, 'sd '
declare @id int
set @id=5
SELECT max(id) from testA
where id <@id and [group]=
(select [group] from testA where id=@id)
