关闭

一个SQL面试题

标签: SQLSQL面试
1176人阅读 评论(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的话,表示这个成绩是这个科目下中的前三的,所以这就是条件。

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

1
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

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

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

SQL Server常见面试题

sql理论题 1.触发器的作用?   答:触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的...
  • dl0914791011
  • dl0914791011
  • 2013-11-25 09:54
  • 7195

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

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

oracle的按月统计sql(一个数据库面试题)

原文地址:oracle的按月统计sql(一个数据库面试题) 题目:   两个数据库表,schema如下:   CREATE TABLE REG_USERS (   I...
  • peterxiaoq
  • peterxiaoq
  • 2014-01-02 15:28
  • 614

C# 面试题源代码大全\一个简单的委托.rar

  • 2011-05-13 10:54
  • 5KB
  • 下载

ajax经典面试题;一个页面实现增删改查之纯JS版

  • 2010-08-09 16:50
  • 85KB
  • 下载

sql面试题java

  • 2017-11-06 20:53
  • 58KB
  • 下载

SQL面试题解析

  • 2014-03-11 13:18
  • 20KB
  • 下载

sql面试题.。

  • 2013-08-23 17:31
  • 3KB
  • 下载

数据库面试题关于SQL

  • 2010-04-21 20:21
  • 170KB
  • 下载
    个人资料
    • 访问:749655次
    • 积分:5469
    • 等级:
    • 排名:第5698名
    • 原创:96篇
    • 转载:1篇
    • 译文:0篇
    • 评论:292条
    博客专栏
    其他信息