表 Aid name [order](排序)
1 张三 1
2 李四 3
3 王五 8
4 刘六 4
5 aaaa 7
6 dddd 2
7 ffff 9
8 bbbb 6
9 cccc 5
……
搜索结果
搜索条件 id>=3firshId lastId recordCount
6 7 7
PS: firshId 是表示搜索出来的数据用[order]进行asc排序,排在第一的id
lastId 是表示搜索出来的数据用[order]进行desc排序,排在第一的id
recordCount 是表示搜索出来的数据总条数
1 张三 1
2 李四 3
3 王五 8
4 刘六 4
5 aaaa 7
6 dddd 2
7 ffff 9
8 bbbb 6
9 cccc 5
……
搜索结果
搜索条件 id>=3firshId lastId recordCount
6 7 7
PS: firshId 是表示搜索出来的数据用[order]进行asc排序,排在第一的id
lastId 是表示搜索出来的数据用[order]进行desc排序,排在第一的id
recordCount 是表示搜索出来的数据总条数
select min(id) first,max(id) lastid,count(1) recordcont from tb where id>3
declare @表A table
(
id int,
name varchar(4),
[order] int
)insert into @表A
select 1,'张三',1 union all
select 2,'李四',3 union all
select 3,'王五',8 union all
select 4,'刘六',4 union all
select 5,'aaaa',7 union all
select 6,'dddd',2 union all
select 7,'ffff',9 union all
select 8,'bbbb',6 union all
select 9,'cccc',5select
(select id from @表A where [order]=(select min([order]) from @表A where id>=3)) as firshId ,
(select id from @表A where [order]=(select max([order]) from @表A where id>=3)) as lastId ,
(select count(1) from @表A where id>=3) as recordCount
/*
firshId lastId recordCount
----------- ----------- -----------
6 7 7
*/
create table 表A
(id int, name varchar(8), [order] int)insert into 表A
select 1, '张三', 1 union all
select 2, '李四', 3 union all
select 3, '王五', 8 union all
select 4, '刘六', 4 union all
select 5, 'aaaa', 7 union all
select 6, 'dddd', 2 union all
select 7, 'ffff', 9 union all
select 8, 'bbbb', 6 union all
select 9, 'cccc', 5
with t as
(select *
from 表A
where id>=3
)
select
(select id from t b where b.[order]=min(a.[order])) 'firshId',
(select id from t b where b.[order]=max(a.[order])) 'lastId',
count(*) 'recordCount'
from t a-- 结果
firshId lastId recordCount
----------- ----------- -----------
6 7 7(1 row(s) affected)
SELECT
(SELECT TOP 1 id FROM dbo.A WHERE id >= 3 ORDER BY [order]) firshId ,
(SELECT TOP 1 id FROM dbo.A WHERE id >= 3 ORDER BY [order] DESC) lastId ,
COUNT(*) AS recordCount FROM dbo.A
WHERE id >= 3