求一sql语句

[山野市民]

表 A

id 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>=3

firshId lastId recordCount
6 7 7
PS: firshId 是表示搜索出来的数据用[order]进行asc排序，排在第一的id
lastId 是表示搜索出来的数据用[order]进行desc排序，排在第一的id
recordCount 是表示搜索出来的数据总条数

[snbxp]

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',5

select
(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
*/ 

[snbxp]

[Quote=引用 1 楼 snbxp 的回复:]
SQL code

select min(id) first,max(id) lastid,count(1) recordcont from tb where id>3
[/Quote]


晕

[唐诗三百首]

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)

[j273558569]

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