select count(*) number,r.* from (select * from student s,class c where s.cid = c.id) r group by r.className order by number; 前5根据不同的数据库,你写不同的条件,你没说,我也就没写
如果是oracle 排序后用 rownum <5; 关键字
select age as 年龄,sname as 姓名,cid as 班级 from student where cidin (select top 2cidfrom student group by cidorder bycount(cid)desc) 以上是使用sql server2005操作的结果,student表保存学生信息,class保存班级信息,student表的cid列为 class表的主键,当然student表可以有N个列,你自己顺便定义,这里只是演示一下; 不知是否合乎你的题意?
假设表结构为: create table class ( cId int primary key identity(1,1), cName nvarchar(10) not null )create table student ( stuId int primary key identity(1,1), classId int references class(cId), stuName nvarchar(10) not null ) SQL查询: select * from class where cid in ( select top 2 classId from student group by classId order by count(classId) desc)
前5根据不同的数据库,你写不同的条件,你没说,我也就没写
rownum <5; 关键字
(select top 2 cid from student group by cid order by count(cid) desc)
以上是使用sql server2005操作的结果,student表保存学生信息,class保存班级信息,student表的cid列为
class表的主键,当然student表可以有N个列,你自己顺便定义,这里只是演示一下;
不知是否合乎你的题意?
create table class
(
cId int primary key identity(1,1),
cName nvarchar(10) not null
)create table student
(
stuId int primary key identity(1,1),
classId int references class(cId),
stuName nvarchar(10) not null
)
SQL查询:
select * from class where cid in
( select top 2 classId from student group by classId order by count(classId) desc)