面试中遇到sql,进行查询

在面试中遇到了这样的问题，不知道大家是怎么解决的，是关于sql的。

我是这么写的。

 

在数据库库中建这样一张表

/****** Object:  Table [dbo].[Student]    Script Date: 2009-1-15 16:46:41 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student]
GO

/****** Object:  Table [dbo].[Student]    Script Date: 2009-1-15 16:46:41 ******/
CREATE TABLE [dbo].[Student] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [lesson] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [mark] [int] NULL
) ON [PRIMARY]
GO

里面的值：有很多的我只写了部分。

 

id	name	lesson	mark
1	tom	English	90
2	lin	Math	80
3	john	Chinese	70
4	tom	Math	87
5	tom	Chinese	78
6	lin	English	87
7	lin	Chinese	88
8	john	Math	45
9	john	English	55
10	aaa	Chinese	56
11	aaa	Math	67
12	aaa	English	87
13	bbb	Chinese	90
14	bbb	Math	78
15	bbb	English	87
16	ccc	Chinese	43
17	ccc	Math	50
18	ccc	English	56
19	ddd	Chinese	98
20	ddd	Math	78
21	ddd	English	80
22	eee	Chinese	87
23	eee	Math	77
24	eee	English	87

 

 

 

select * from Student

--1.有不及格学科学生名字
select distinct name from Student where mark<60
--2.超出一门学科不及格的学生名字
select distinct name from Student where mark<60

--3.所有学科都不及格
  ---第一种
 select distinct name from Student as s where  name in(select distinct name from Student)  and (select mark from Student where lesson= 'Chinese'  and name=s.name ) <60  and
(select mark from Student where lesson='Math' and name=s.name ) <60  and (select mark from Student where lesson='English' and name=s.name ) <60

--4.Math排名前三包括并列
 
select top 3 name, lesson,mark from Student  where lesson='Math' order by(mark)  desc

--5.总分排名前三（包括并列）
select top 3 name ,sum(mark) as total from Student group by(name) order by(total) desc

--6.列出每一科分数最高者名字及分数
 select name ,l.lesson,l.maxmark  from ( select lesson,  max(mark) as maxmark  from Student group by(lesson)) as l,Student s
where s.mark=l.maxmark and s.lesson=l.lesson

 

评论

4 楼 love1907 2009-01-16  

1,select distinct name from Student where mark<60
2,select name ,count(name) as count_na from Student where mark<60 group by name having count_na >=2
3,select name  from Student stu where not exists (select 1 from Student stu1 where stu1.id=stu.id and stu1.mark>=60)
4,select name from Student stu order by stu.mark desc limit 0,3
5,select name,sum(stu.mark) as sum_ma from Student stu group by stu.name order by sum_ma  desc limit 0,3
6,select name,lesson,mark  from student stu1 where exists (select max(stu.mark) as max_mark from Student stu group by stu.lesson having max_mark = stu1.mark   )

3 楼 cuiran 2009-01-16  

xuyao 写道

2是写错了，呵呵 还有就是第三名有两个呢，你不top4吗？

如果正好前三名就三个，分数也不等，top4不就查前四个了。

2 楼 xuyao 2009-01-15  

2是写错了，呵呵
还有就是第三名有两个呢，你不top4吗？

1 楼 zfigo_000 2009-01-15  

超出一门学科不及格的学生名字
select distinct name from Student where mark<60
这个写错了

select name from Student group by name having count(name) > 1 and mark<60

--3.所有学科都不及格
这个你写的非常不好。