50个查询系列-第8个查询:查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;...
时间: 2023-09-11 21:09:22 浏览: 48
以下是查询语句:
```
SELECT s.sno, s.sname
FROM score sc1, score sc2, student s
WHERE sc1.sno = sc2.sno
AND sc1.cno = '002'
AND sc2.cno = '001'
AND sc1.score < sc2.score
AND s.sno = sc1.sno;
```
解释:
1. 使用两个 score 表来比较课程编号 “002” 和课程编号 “001” 的成绩;
2. 通过学生表 student 来获取学生的学号和姓名;
3. 在 WHERE 子句中,使用 sc1.sno = sc2.sno 来限制两个 score 表的学生编号相同;
4. 使用 sc1.cno = '002' 和 sc2.cno = '001' 分别限制课程编号;
5. 使用 sc1.score < sc2.score 来比较成绩大小;
6. 最后限制查询结果只包含学号和姓名,通过 s.sno = sc1.sno 来连接学生表和 score 表。
相关问题
查询“C001”课程成绩比“C002”课程成绩高的学生姓名、课程名、“计算机导论”课程成绩,多表查询
假设有以下三个表:
学生表(student):
| id | name |
| ---- | ------- |
| 1 | 小明 |
| 2 | 小红 |
| 3 | 小刚 |
| 4 | 小李 |
课程表(course):
| id | name |
| ---- | ---------- |
| C001 | 计算机导论 |
| C002 | 数据结构 |
成绩表(score):
| id | student_id | course_id | score |
| ---- | ----------| ---------| ------|
| 1 | 1 | C001 | 85 |
| 2 | 1 | C002 | 76 |
| 3 | 2 | C001 | 92 |
| 4 | 2 | C002 | 88 |
| 5 | 3 | C001 | 80 |
| 6 | 3 | C002 | 82 |
| 7 | 4 | C001 | 78 |
| 8 | 4 | C002 | 90 |
可以使用以下 SQL 语句实现查询:
```
SELECT s.name AS student_name, c.name AS course_name, s1.score AS score
FROM score s1
JOIN student s ON s.id = s1.student_id
JOIN course c ON c.id = s1.course_id
JOIN (SELECT student_id, score FROM score WHERE course_id = 'C001') s2 ON s2.student_id = s1.student_id
WHERE s1.course_id = 'C002' AND s1.score < s2.score;
```
以上 SQL 语句中,第一个 JOIN 子句将成绩表(score)与学生表(student)连接,第二个 JOIN 子句将成绩表(score)与课程表(course)连接,第三个 JOIN 子句将成绩表(score)自连接,获取“C001”课程的成绩,然后通过 WHERE 子句筛选出“C002”课程成绩比“C001”课程成绩高的学生,并输出学生姓名、课程名、“计算机导论”课程成绩。
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 sql语句怎么写
假设有以下三张表:
**student表**
```
id name age gender
1 Tom 18 M
2 Jack 19 M
3 Lily 18 F
4 Lucy 20 F
5 Bob 19 M
```
**course表**
```
id name
1 Math
2 English
3 Science
```
**score表**
```
id student_id course_id score
1 1 1 80
2 2 1 90
3 3 1 85
4 4 1 95
5 5 1 88
6 1 2 75
7 2 2 85
8 3 2 90
9 4 2 95
10 5 2 80
11 1 3 90
12 2 3 85
13 3 3 92
14 4 3 88
15 5 3 95
```
则查询所有课程的成绩第2名到第3名的学生信息及该课程成绩的 SQL 语句如下:
```
SELECT s.name, c.name, sc.score
FROM (
SELECT course_id, student_id, score, DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC) AS rank
FROM score
) AS sc
INNER JOIN student AS s ON sc.student_id = s.id
INNER JOIN course AS c ON sc.course_id = c.id
WHERE sc.rank BETWEEN 2 AND 3
ORDER BY c.name, sc.score DESC;
```
解释:
1. 内层子查询使用了窗口函数 `DENSE_RANK()`,按照课程分组,按照成绩从高到低排名,生成一个排名列 `rank`。
2. 外层查询将内层查询结果进行关联,得到学生姓名、课程名称和成绩,并筛选出排名在 2-3 之间的结果。
3. 最后按照课程名称和成绩从高到低排序。