2009-02-23面试题

table如下，共有5个科目，所有的题目都必须用一条语句处理：
+---------+-------------+------+
| name    |lesson      |mark  |
+---------+-------------+------+
| John    | Math        | 60   |
| Mike    | Eng         | 70   |
| Mark    | History     | 80   |
+---------+-------------+------+

1.有一科不及格的学生名单

2.不及格科目超过2门的学生名单

3.所有科目都不及格的学生名单

4.总分前三的所有学生名单（包括并列）（这个问题最让我头疼，limit，top都不好使，因为并列条件的存在使得结果可能超过3条）

5.各科成绩最高的所有学生名单（包括并列）

评论

10 楼 hanwei59 2009-02-27  

select t.name,sum(t.mark) totlemark
from marktable t 
group by name
having sum(t.mark)>=
(--括号里查出来第三名的总分
  select totlemark from (
    select rownum rn,t2.totlemark 
    from (
      select distinct sum(t.mark) totlemark 
      from marktable t 
      group by name order by sum(t.mark) desc
    ) t2
  ) t where t.rn=3
)
order by sum(t.mark) desc;

9 楼 贫嘴男孩 2009-02-25  

hanwei59 写道

貌似我搞的很复杂 

你的太复杂了

8 楼 hanwei59 2009-02-25  

貌似我搞的很复杂 

7 楼 hanwei59 2009-02-25  

第四题

select 
  t1.name,t1.totlemark 
from (
  select rownum rn,t2.name,t2.totlemark 
  from (
    select t.name,sum(t.mark) totlemark 
    from marktable t 
    group by name order by sum(t.mark) desc
  ) t2
) t1
where t1.totlemark>=(
  select totlemark from (
    select rownum rn,t2.totlemark 
    from (
      select distinct sum(t.mark) totlemark 
      from marktable t 
      group by name order by sum(t.mark) desc
    ) t2
  ) t where t.rn=3)
order by t1.totlemark desc;

ps:全帮主的romNum=3不对吧

6 楼 风花雪月饼 2009-02-25  

4.总分前三的所有学生名单（包括并列） 用having实现

select name,sum(mark) mk from marks m1 group by name
having mk>=
(
select mk from
(select name,sum(mark) mk from marks m2 group by name) tmp
group by mk order by mk DESC limit 2,1
)
order by mk desc

这个方式是通过取得第三名的成绩，然后找出大于等于第三名成绩的学生
其中与前一个思路不一样的地方：

select mk from
(select name,sum(mark) mk from marks m2 group by name) tmp
group by mk order by mk DESC limit 2,1

通过取得前三的成绩，取得第三名的总分。

然后使用得到的总分比较就取得了前三。

SQL代码依旧难看。

5 楼 风花雪月饼 2009-02-25  

4.总分前三的所有学生名单（包括并列）

select t1.name Name,t1.mk MarkSum from
(select name,sum(mark) mk from marks m1 group by name) t1
inner join
(
  select mk from
  (select name,sum(mark) mk from marks m2 group by name) tmp
  group by mk order by mk desc limit 3
) t2
on t1.mk=t2.mk order by MarkSum desc

稍微讲解一下
1.查得所有学生的总成绩

(select name,sum(mark) mk from marks m1 group by name) t1

2.查出前三的分数，用group过滤掉相同的分数,mysql里不能将Distinct用在sum字段上，取得前三的分数

  select mk from
  (select name,sum(mark) mk from marks m2 group by name) tmp
  group by mk order by mk desc limit 3

3.将上述两个查询数据集合通过总成绩关联起来。

... inner join ... on t1.mk=t2.mk order by MarkSum desc

4 楼 风花雪月饼 2009-02-25  

留个记录先
http://iknow.baidu.com/question/36134482.html

擦。一模一样的题目。。。明天再研究

3 楼 风花雪月饼 2009-02-25  

1.有一科不及格的学生名单
select name from marks where mark<60 group by name

2.不及格科目超过2门的学生名单
select name from marks where mark<60 group by name having count(name)>2

3.所有科目都不及格的学生名单
select name from marks where mark<60 group by name having count(name)=5

2 楼 贫嘴男孩 2009-02-23  

全帮主，麻烦你把sql语句整理一下再发，我看得好头大

1 楼 全冠清 2009-02-23  

第四题

select name,totlemark    from 
(select name,sum(mark) as totlemark from table group by name having sum(mark)
order by sum(mark)) as temp1
where rowNum>(select rowNum form temp1 where name=(select name from temp1 order by sum(mark) where totlemark =
(select distinct totlemark from temp1 where romNum=3))