try:
select top n from [table] group by classid
select top n from [table] group by classid
解决方案 »
- 怎么用SQL 实现下面的逻辑?
- sql2008在筛选表时候出错
- 小弟初来咋到 请高手帮忙解决sql问题
- 经常出现 sql server 2000 异常: 所提供的游标标识符值(0)无效
- 为什么输出的文本格式是这样的呢??
- 怎样在查询分析器中将SQL2000查询结果保存为XLS文件
- 在98下,sql server企业管理器进不了数据库,而服务照样能启动,什么问题?
- 使用vs.2005怎样修改sql 2005的密码
- 怎样将建立好的数据库安装到其他的SQL Server 2000上?
- sqlserver工具如何导入excel数据
- 棘手啊,TEXT参数的处理,很急~~~
- 求救SQL-SERVER Setup问题
select top n * from [table] group by classid
where Name in (select top N Name
from 表
where ClassID=t.ClassID
order by Name)
a.*
from
[table] a
where
a.Name in(select top n Name from [table] where ClassID=a.ClassID)
否则就不对
就加一个自动增长的列id标识一下
改成:
select
a.*
from
[table] a
where
a.id in(select top n id from [table] where ClassID=a.ClassID)
表 Student:
-----------------------------
ClassID | StudentName
------------|----------------
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
--------------------------------需要的结果集:(各班取2个)
-----------------------------
ClassID | StudentName
------------|----------------
1 a
1 b
2 d
2 e
3 g
3 h
and a.StudentName<=b.StudentName)<=2
--测试环境
declare @t table(ClassID int,StudentName varchar(20))
insert into @t select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 2,'d'
union all select 2,'e'
union all select 2,'f'
union all select 3,'g'
union all select 3,'h'--查询
select * from @t A
where StudentName in
(select top 2 StudentName from @t where ClassID=A.ClassID
Order by ClassID)
--结果
ClassID StudentName
----------- --------------------
1 a
1 b
2 d
2 e
3 g
3 h(所影响的行数为 6 行)
(ClassID int,StudentName varchar(10))
insert into @t values (1,'a')
insert into @t values (1,'b')
insert into @t values (1,'c')
insert into @t values (2,'d')
insert into @t values (2,'e')
insert into @t values (2,'f')
insert into @t values (3,'g')
insert into @t values (3,'h')select * from @t a where (select count(1) from @t b where a.ClassID=b.ClassID
and a.StudentName<=b.StudentName)<=2ClassID StudentName
----------- -----------
1 b
1 c
2 e
2 f
3 g
3 h(所影响的行数为 6 行)