已知某班级有30名学生,期末要参加5门考试。
下表记录着所有学生所有学科的成绩,该表名称为“Student”
Name Lesson Mark
Tom Eng 90
Lin Mic 80
John Math 70
...............
请分别用一句sql语句来完成下列查询:
一。列出不及格学科(Mark<60)的学生名字
二。列出有超过一门学科不及格的学生名字
三。列出所有学科都不及格的学生名字
四。列出Math排名前三(包括并列)的学生名字
五。列出总分排名前三(包括并列)的学生名字
六。列出每门学科最高分数的获得者名字及分数(包括并列)
下表记录着所有学生所有学科的成绩,该表名称为“Student”
Name Lesson Mark
Tom Eng 90
Lin Mic 80
John Math 70
...............
请分别用一句sql语句来完成下列查询:
一。列出不及格学科(Mark<60)的学生名字
二。列出有超过一门学科不及格的学生名字
三。列出所有学科都不及格的学生名字
四。列出Math排名前三(包括并列)的学生名字
五。列出总分排名前三(包括并列)的学生名字
六。列出每门学科最高分数的获得者名字及分数(包括并列)
1. select distinct name from where <60;
2. select distinct name from where <60 group by name having count(name)>1;
3. select distinct name from where <60 group by name having count(name)=5;
4. select distinct name from where name in (select name from(select name ,dense_rank() over(partition by lession order by ) rr from where lesson='Math') where rn<=3);
1. SELECT DISTINCT name
FROM
WHERE <60;
2. SELECT DISTINCT name
FROM
WHERE <60
GROUP BY name
HAVING COUNT(name)>1;
3. SELECT DISTINCT name
FROM
WHERE < 60
GROUP BY name
HAVING COUNT(name) = 5;
4. SELECT *
FROM student A,
(SELECT DISTINCT
FROM student
WHERE lesson = 'Math'
AND ROWNUM <= 3
ORDER BY DESC) B
WHERE A.lesson = 'Math'
AND A. = B.5. SELECT name
FROM (SELECT name, SUM() AS
FROM student
GROUP BY name
ORDER BY DESC) A
WHERE A. IN (SELECT DISTINCT
FROM (SELECT name, SUM() AS
FROM student
GROUP BY name
ORDER BY DESC)
WHERE ROWNUM <= 3))
6.SELECT *
FROM student A,
(SELECT lesson, MAX() AS FROM student GROUP BY lesson) B
WHERE A.lesson = B.lesson
AND A. = B.SQL写的比较烂哈 呵呵
FROM student A,
(SELECT DISTINCT
FROM student
WHERE lesson = 'Math'
AND ROWNUM <= 3
ORDER BY DESC) B
WHERE A.lesson = 'Math'
AND A. = B.
5. SELECT name
FROM (SELECT name, SUM() AS
FROM student
GROUP BY name
ORDER BY DESC) A
WHERE A. IN (SELECT DISTINCT
FROM (SELECT name, SUM() AS
FROM student
GROUP BY name
ORDER BY DESC)
WHERE ROWNUM <= 3))
5:
SELECT NAME
FROM (
SELECT NAME,
DENSE_RANK() OVER(ORDER BY SUM(MARK) DESC) DR
FROM STUDENT S
GROUP BY NAME
)
WHERE DR <=3;
6:
SELECT NAME,
LESSION,
MARK
FROM (
SELECT NAME,
LESSION,
MARK,
DENSE_RANK() OVER(PARTITION BY LESSION ORDER BY MARK DESC) DR
FROM STUDENT S
)
WHERE DR = 1;