连续两次参加面试,都遇到了同样的笔试题目,只怨楼主懒惰没有搞清楚,现请高手赐教。
题目大概是这样:有两个表,班级表(class)中有name,id两个字段,分别表示班级名称和班级id,学生表(student)中有name,id,classid三个字段,分别表示学生姓名,学生学号,学生所在班级号。要求:查询人数最多的前5个班级的班级名称和班级人数。不限制用哪种数据库
题目大概是这样:有两个表,班级表(class)中有name,id两个字段,分别表示班级名称和班级id,学生表(student)中有name,id,classid三个字段,分别表示学生姓名,学生学号,学生所在班级号。要求:查询人数最多的前5个班级的班级名称和班级人数。不限制用哪种数据库
select a.name,a.id,b.num from class a inner join(
select classid,count(*) num from student group by classid
) b
on a.id = b.classid order by b.num desc limit 5
from student A,class B
where A.id=B.classid
group by A.name
order by num desc
limit 5;
join student as s on s.classid=c.id
group by c.name
order by num desc
From Class A
Left Join Student B On A.Id = B.ClassID
Group By A.ID
Order By number Desc, ClassID Asc
select top 5 a.name,b.num from class a
left join (select classid,count(*) as num
from student
group by classid) B --先把每个班的人数统计出来
on a.id = b.classid
order by B.num
select cname,B.csum from class inner join(
select top 5 scid,count(*) as csum from student group by scid order by csum desc) B
on B.scid=class.cid order by B.csum desc
if exists(select * from sysobjects where name = 'class')
drop table class
go
create table class
(
id int primary key identity(1,1),
name nvarchar(20)
)
go
if exists(select * from sysobjects where name = 'student')
drop table student
go
create table student
(
id int primary key identity(1,1),
name nvarchar(20),
classid int foreign key references class(id)
)
go
select * from class
select * from student
goinsert into class
select '高一(1)' union all
select '高一(2)' union all
select '高一(3)' union all
select '高一(4)' union all
select '高一(5)' union all
select '高一(6)' union all
select '高一(7)'
go
insert into student
select 'A',1 union allselect 'B',2 union all
select 'C',2 union all
select 'D',2 union allselect 'E',3 union all
select 'F',4 union all
select 'G',4 union all
select 'H',4 union allselect 'I',5 union all
select 'J',5 union all
select 'K',5 union all
select 'T',5 union all
select 'L',5 union allselect 'M',6 union all
select 'N',6 union all
select 'O',6 union allselect 'P',7 union all
select 'Q',7 union all
select 'R',7 union all
select 'S',7 go
select * from class
select * from student
go
--查询人数最多的前5个班级的班级名称和班级人数。
select x.name,y.num from class x
inner join
(
--(前5个人数最多的)班级id和人数
select top 5 classid,num from
(
--班级id和班级人数
select classid,count(1)num from student group by classid
)a order by num desc
) y on x.id = y.classid
where a.id=b.classid group by a.name order by 2 desc
--查询人数最多的前5个班级的班级名称和班级人数。
select x.name,y.num from class x
inner join
(
--(前5个人数最多的)班级id和人数
select top 5 classid,num from
(
--班级id和班级人数
select classid,count(*)num from student group by classid
)a order by num desc
) y on x.id = y.classid
go
--第2种写法
with t as(
--查找各班级的人数
select classid,count(*)num from student
group by classid
)
select x.name,t.num from class x
inner join
(
--人数倒序,取前5个
select top 5 num,classid from t order by num desc
)t on x.id = t.classid
'class' 附近有语法错误。
少了个from,这写法效率挺高的,学习啦
IF OBJECT_ID('dbo.Class')IS NOT NULL
DROP TABLE Class
IF OBJECT_ID('dbo.Student')IS NOT NULL
DROP TABLE Student
CREATE TABLE Class
(ID int,
NAME VARCHAR(10)
)
CREATE TABLE Student
(
ID INT
,NAME varchar(10)
,ClassID int
)INSERT INTO class
VALUES
(1,'1班'),(2,'2班'),(3,'3班')
,(4,'4班'),(5,'5班'),(6,'6班')INSERT INTO Student VALUES
(1,'zhang1',1),(2,'zhang2',1),(3,'zhang3',1),
(4,'wd',2),(5,'wd1',2),(6,'wd2',2),
(7,'sw1',3),(8,'sw2',3),(9,'sw3',3),
(10,'QQ',4),(11,'PS',4),(12,'td',4),
(13,'350',5),(14,'LIVE',5),(15,'Qm',5),
(16,'buy',6),(17,'ZWS',6),(18,'WJ',3)SELECT TOP 5 B.name,count(A.ID) as num
from student A,class B
where A.classid=B.id
group by b.name
order by num desc
name num
3班 4
5班 3
4班 3
2班 3
1班 3
as
(select a.name,COUNT(b.id) as cnt
from class a,student b
where a.id = b.classid
group by a.name)select top 5 * from
(select *,row_number()over(order by cnt desc) as pm from cte) x