题目

1 查询” 01 “课程比 “ 02 “课程成绩高的学生的信息及课程分数

1
2
3
4
5
6
7
8
9
SELECT
  A.*,
  B.cid,
  B.score
FROM
  ( SELECT sid, cid, score FROM sc WHERE cid = "01" ) A
  LEFT JOIN ( SELECT sid, cid, score FROM sc WHERE cid = "02" ) B ON A.sid = B.sid
WHERE
  A.score > B.score

1.1 查询同时存在” 01 “课程和” 02 “课程的情况

1
2
3
4
5
6
7
8
9
SELECT
  A.*,
  B.cid,
  B.score
FROM
  ( SELECT sid, cid, score FROM sc WHERE cid = "01" ) A
  LEFT JOIN ( SELECT sid, cid, score FROM sc WHERE cid = "02" ) B ON A.sid = B.sid
WHERE
  b.sid IS NOT NULL

1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为null)

1
2
3
4
5
6
7
SELECT
  A.*,
  B.cid,
  B.score
FROM
  ( SELECT sid, cid, score FROM sc WHERE cid = "01" ) A
  LEFT JOIN ( SELECT sid, cid, score FROM sc WHERE cid = "02" ) B ON A.sid = B.sid

1.3 查询不存在” 01 “课程但存在” 02 “课程的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
  sid,
  cid,
  score
FROM
  sc
WHERE
  cid = '02'
  AND sid NOT IN (
  SELECT
    sid
  FROM
    sc
  WHERE
    cid = '01')

2 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩