一个SQL面试题

标签: SQL SQL面试
1387人阅读 评论(1) 收藏 举报
分类:

......一个看似简单,实则容易踩坑的例子

    记得以前有次被面试问到SQL,当时没答上来,当时也没鼓捣出来(当时对SQL也确实太不熟了(⊙o⊙))。

    今天突然想起来了,就重新拿出来,解决后在此处记录一下。

    切入正题(下面基于Oracle):

    表是这样一张表:

  1. CREATE SEQUENCE seq_grade
  2. increment by 1 -- 每次递增1
  3. start with 1 -- 1开始
  4. nomaxvalue -- 没有最大值
  5. minvalue 1 -- 最小值=1
  6. NOCYCLE; -- 不循环
  7. --
  8. create table grade(
  9. id number primary key,
  10. student nvarchar2(10),
  11. course nvarchar2(10),
  12. grade number
  13. );
  14. --
  15. insert into grade values(seq_grade.nextval,'tom', 'math',64);
  16. insert into grade values(seq_grade.nextval,'tom', 'english',34);
  17. insert into grade values(seq_grade.nextval,'tom', 'cpp',67);
  18. insert into grade values(seq_grade.nextval,'bob', 'math',23);
  19. insert into grade values(seq_grade.nextval,'bob', 'english',89);
  20. insert into grade values(seq_grade.nextval,'bob', 'cpp',45);
  21. insert into grade values(seq_grade.nextval,'lily', 'math',78);
  22. insert into grade values(seq_grade.nextval,'lily','english',89);
  23. insert into grade values(seq_grade.nextval,'lily', 'cpp',34);
  24. insert into grade values(seq_grade.nextval,'timo', 'math',56);
  25. insert into grade values(seq_grade.nextval,'timo','english',79);
  26. insert into grade values(seq_grade.nextval,'timo', 'cpp',34);
  27. insert into grade values(seq_grade.nextval,'ez', 'math',78);
  28. insert into grade values(seq_grade.nextval,'ez', 'english',56);
  29. insert into grade values(seq_grade.nextval,'ez', 'cpp',98);

    解释:seq_grade是用于grade表的id自增长而创建的。

    看下数据:

  1. tom math 64
  2. tom english 34
  3. tom cpp 67
  4. bob math 23
  5. bob english 89
  6. bob cpp 45
  7. lily math 78
  8. lily english 89
  9. lily cpp 34
  10. timo math 56
  11. timo english 79
  12. timo cpp 34
  13. ez math 78
  14. ez english 56
  15. ez cpp 98

    问题是:得到每科成绩前3的人的所以信息。

    涉及到这种问题,首先就想到用科目来分组,可这就是一个很大的坑,因为 group by 如果不与聚合函数结合使用,是毫无意义的,甚至在有些数据库(比如Oracle)是会报错的。

    下面就直接给出正确的SQL:

  1. select * from grade g
  2. where(
  3. select count(*) from grade where g.course=course and g.grade<grade
  4. ) < 3
  5. order by course,grade desc;

    结果:

  1. 45 ez cpp 98
  2. 33 tom cpp 67
  3. 36 bob cpp 45
  4. 35 bob english 89
  5. 38 lily english 89
  6. 41 timo english 79
  7. 37 lily math 78
  8. 43 ez math 78
  9. 31 tom math 64

    结果肯定是对的。

    如果使用java代码,我们可以先取出一个科目的列表,然后迭代取出他们的前三,最后组合。但是这样会造成多次访问数据库,效果并不理想。那么要把这个过程原子化也可以选择使用存储过程,但是总感觉有点滥用存储过程了。

    所以使用SQL依然是此处的最佳选择。

    上面的SQL,难点还是在where子句中的子查询:我们吧最外层的select看做一个循环,它遍历的是经过了排序后的行,然后条件中的子查询就是从遍历的时候,使用当前的值,与该表的相同科目下成绩的对比(相当于连表查询了),并且这样的结果的个数小于3的话,表示这个成绩是这个科目下中的前三的,所以这就是条件。

    当然这里没有考虑第三名与后面的成绩相同的情况。

查看评论

SQL常见的一些面试题(太有用啦)

SQL常见面试题 1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name   kecheng   fenshu 张三    语文       81 张三     数学   ...
  • a379850992
  • a379850992
  • 2017-02-18 09:54:51
  • 20539

SQL数据库面试题以及答案(50例题)

SQL数据库面试题以及答案(50例题)
  • hundan_520520
  • hundan_520520
  • 2017-02-05 15:13:36
  • 52659

sql常见面试题 (我觉得太好了)

Sql常见面试题 受用了 1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name   kecheng   fenshu 张三     语文       81 张三    ...
  • u012467492
  • u012467492
  • 2015-07-07 15:29:15
  • 120963

【笔试/面试】SQL 经典面试题

基本概念 (1)any/all,构成 where 子句的条件判断,any:表示或(or)的概念,all:则表示与(and)的概念,这两个关键字的出现是为了语句的简化; (2)先分组再做聚合,逻辑上也应...
  • lanchunhui
  • lanchunhui
  • 2016-04-05 23:17:49
  • 10966

SQL经典面试题及答案

转载自:http://blog.csdn.net/hwq1987/article/details/6670300 SQL经典面试题及答案 2007年07月27日 星期五 上午 08:42 ...
  • wickedvalley
  • wickedvalley
  • 2016-06-16 17:27:48
  • 25227

SQL一般面试题

几个表 employees 表:  EMPLOYEE_ID              NUMBER(6)  FIRST_NAME             ...
  • qq_36864672
  • qq_36864672
  • 2017-09-20 08:15:30
  • 504

LeetCode刷题指南 Java版

LeetCode刷题指南,文章内容来源于@CyC2018的leetcode题解。Java工程师LeetCode刷题必备。按照该指南的刷题顺序我做了一定的分类和注释,有些题目虽然复杂度不是最优解,但是比...
  • a724888
  • a724888
  • 2017-03-31 20:24:10
  • 5690

SQL语句面试题目:一般查询和高级子查询

with子句-----给子查询定义一个别名,可以通过别名来引用子查询,实现了一次定义多次使用。 WITH子句的格式为: WITH 别名 AS(子查询) 定义好别名就可以在sql中通过别名来引用子...
  • CSDN_Terence
  • CSDN_Terence
  • 2017-09-16 11:15:57
  • 7179

Sql常见面试题 受用了

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name   kecheng   fenshu 张三    语文       81 张三     数学       75 李四  ...
  • LeehomeYU
  • LeehomeYU
  • 2017-04-22 14:26:14
  • 1415

SQL笔试面试题总结

SQL笔试面试题总结1)数据库表建立复合索引tab_index(“name”, “age”),下面那些语句能用上索引?(C) A)select *from table where age=18 a...
  • yzhang6_10
  • yzhang6_10
  • 2016-11-19 12:26:15
  • 4526
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 88万+
    积分: 5831
    排名: 5512
    博客专栏
    music