大数据学习第十三天（mysql不会的查询2）

1、数据
CREATE TABLE Student
(
    s_id    VARCHAR(20) COMMENT '学生编号',
    s_name  VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
    s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
    s_sex   VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
    PRIMARY KEY (s_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT '学生表';
-- 课程表
CREATE TABLE Course
(
    c_id   VARCHAR(20) COMMENT '课程编号',
    c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
    t_id   VARCHAR(20) NOT NULL COMMENT '教师编号',
    PRIMARY KEY (c_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT '课程表';
-- 教师表
CREATE TABLE Teacher
(
    t_id   VARCHAR(20) COMMENT '教师编号',
    t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
    PRIMARY KEY (t_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT '教师表';
-- 成绩表
CREATE TABLE Score
(
    s_id    VARCHAR(20) COMMENT '学生编号',
    c_id    VARCHAR(20) COMMENT '课程编号',
    s_score INT(3) COMMENT '分数',
    PRIMARY KEY (s_id, c_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT '成绩表';

2、表的显示

        1）课程表

        2）成绩表

        3）学生表

        4）教师表

3、题目

1）查询每门功课成绩最好的前两名
（1）with tb1 as
(
    select
        *,dense_rank() over (partition by c_id order by s_score desc ) drk
    from
        score
)
select *
from tb1
where
drk<=2;

（2）select * from (select c_id,s_score from score where c_id='01' order by s_score desc limit 2) tb2
union all (select c_id,s_score from score where c_id='02' order by s_score desc limit 2)
union all (select c_id,s_score from score where c_id='03' order by s_score desc limit 2);

2）查询不同课程,成绩相同的学生的学生编号、课程编号、学生成绩
-- 查询学生中所考试成绩一样的
select s.s_id,
       max(if(c.c_id = '01',s_score, 0)) as `01`,
       max(if(c.c_id = '02',s_score, 0)) as `02`,
       max(if(c.c_id = '03',s_score, 0)) as `03`
from student
         left join exam.score s on student.s_id = s.s_id
         left join exam.course c on s.c_id = c.c_id
group by s.s_id
having `01`=`02` and `02`=`03` and s.s_id is not null;