SQl查询语句求助,急，在线等!

[nbtvujwc]

求助如下SQL要怎么写？

字段1 字段2 字段3
学号 班级 年级
0400001 04000001 2004
0400002 04000001 2004
0400003 04000001 2004
0400004 04000001 2004
0400005 04000001 2004
0400006 04000001 2004
0400007 04000002 2004
0400008 04000002 2004
0400009 04000002 2004
0400010 04000002 2004
0400011 04000002 2004
0400012 04000002 2004
0400013 04000002 2004
0500007 05000002 2005
0500008 05000002 2005
0500009 05000002 2005
0500010 05000002 2005
0500011 05000002 2005
0500012 05000002 2005
0500013 05000002 2005


查询要求：找出同一年级且同一班代码的学生中的首二个学号和最后二个学号！

[nbtvujwc]

请问第一个FROM后面的表和第二个FROM 后面的表，在我这里是同一个表，但安照这样做的话结果显示为未区分年级，班级代码的最前2条和最后2条。

如上面数据执行，则显示结果为
0400001 04000001 2004
0400002 04000001 2004

0500012 05000002 2005
0500013 05000002 2005
这样了，但这不是我要的结果，我要的结果如下：分年级分班级的最前两条，最后两条！
0400001 04000001 2004
0400002 04000001 2004
0400005 04000001 2004
0400006 04000001 2004

0400007 04000002 2004
0400008 04000002 2004
0400012 04000002 2004
0400013 04000002 2004

0500007 05000002 2005
0500008 05000002 2005
0500012 05000002 2005
0500013 05000002 2005

[dawugui]

select * from #tab t
where 学号 in
(
select top 2 学号 from #tab where 年级=t.年级 and 班级=t.班级 order by 学号
)


select * from #tab t
where 学号 in
(
select top 2 学号 from #tab where 年级=t.年级 and 班级=t.班级 order by 学号 desc
)
order by 年级,班级,学号 desc

[冷箫轻笛]

select * from tablename t1
where 学号 in (select top 2 id from tablename where 年级= t1.年级 and 班级= t1.班级)
or 学号 in (select top 2 id from tablename where 年级= t1.年级 and 班级= t1.班级 order by 学号 desc)

[dawugui]

按如下表取某个字段(ID)分类后同时按时间(rdate)升序、降序前三条记录。

if object_id('tempdb..#tab') is not null
drop table #tab
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])

insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01')
insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02')
insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-04')
insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05')
insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06')
insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01')
insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02')
insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03')
insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04')
insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01')
insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02')
insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03')
insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01')
insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02')
insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')

--按时间rdate升序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate
)

id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000

(所影响的行数为 12 行)

--按时间rdate降序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate desc
)
order by id , rdate desc

id age rdate
---------- ----------- ------------------------------------------------------
a 6 2006-01-06 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 4 2006-01-04 00:00:00.000
b 4 2006-02-04 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 2 2006-02-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 1 2006-03-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
d 1 2006-04-01 00:00:00.000
e 1 2006-05-01 00:00:00.000
(所影响的行数为 12 行)


--上面包含了总数不到3个的记录(即id为d,e的数据)，如果要取消它们，以升序为例(降序同理)
select * from #tab m
where rdate in
(
select top 3 rdate from
(
select * from #tab t
where id in
(
select id from #tab group by id having(count(*)) >= 3
)
) n
where m.id = n.id order by rdate
)

id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
(所影响的行数为 9 行)

--在上面的例中我们发现rdate都是不相同的，如果相同怎么办？
--例如id=a，第三条，第四条rdate相同，都为2006-01-03。
id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
a 4 2006-01-03 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 6 2006-01-06 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 4 2006-02-04 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000

--如果想把第三、四都取出来，使用上面的语句即可。如果只取一条(只取第三不取第四)则要使用临时表了。
if object_id('tempdb..#tab') is not null
drop table #tab
GO
if object_id('tempdb..#temp') is not null
drop table #temp
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])

insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01')
insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02')
insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05')
insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06')
insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01')
insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02')
insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03')
insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04')
insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01')
insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02')
insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03')
insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01')
insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02')
insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')

--按时间rdate升序取前三条(其他方法同上)
select id1=identity(int,1,1),* into #temp from #tab order by id , rdate /*(降序用rdate desc)*/
select * from #temp t
where id1 in
(
select top 3 id1 from #temp where id=t.id order by id1
)

[冷箫轻笛]

--这样写好一些
select * from tablename t1
where 学号 in (select top 2 id from tablename where 年级= t1.年级 and 班级= t1.班级)
union all
select * from tablename t1
where 学号 in (select top 2 id from tablename where 年级= t1.年级 and 班级= t1.班级 order by 学号 desc)

[splory]

declare @sql varchar(8000)
set @sql=''
select @sql=@sql='select top 2 学号 from tb where 班级 ='+rtrim(a.班级)+' order by 学号 union all
select top 2 学号 from classtb where 班级 ='+rtrim(a.班级)+' order by 学号 desc union all
' from (select distinct 班级 from tb) a
select @sql=left(@sql,len(@sql)-10)
exec(@sql)