报表开发2-明细报表开发-笔记

背景：
两种任务：
一种：业务系统的开发：（面向用户）
*、注册、登录、选择商品（电商、视频、。。。）、下单、付费、退款、结算、配送。

二种：报表系统的开发：（面向运营，或公司领导）

/*
报表开发:学生考试【明细报表】 。
步骤：先快速产生学生参加加考试的信息，成绩默认为null。
      原因：用传统思路，如果学生参加一科，产生一条数据，
      缺点：有的学生没有来参加，就没有记录，对于未参考学生就不好统计。
*/
--清空表: truncate table score_info;


--如何记录学生的成绩信息？
两种方案：
1、学生考试完一科（有得分），就记录一条。
   特点：没有参加考试的同学，就没有记录。
2、提前生成学生成绩表（无得分）。
   特点：没有参加考试的同学，也有一条记录，只是分数为 null。
哪一种方案好？（思路：每种方案优点和缺点）
第一种：效果比较高（不正确），其实效率比较低。 
        缺点：不好统计没有参加考试的同学。（正确）
第二种：优点：比较好统计不参加考试的同学。 
        “缺点”：没有参加考试的同学，会占一条记录。
特点：下面的成绩表是一次性提交生成的（考试前，管理员，老师进入后台生成）。
       所以在提交时，只需要做update。

select * from class_info;
select * from student_info;  -- 11人
select * from subject_info;  -- 4科
select * from score_info;


--原理：交叉连接，笛卡尔乘积。
-- select * from a cross join b ;
-- select * from a,b;
select * from student_info a,subject_info b;
select * from score_info limit 10;
--语法： insert into ... select ....
delete from score_info where exam_title='二班java摸底考试';
insert into score_info(exam_title,stu_id,subject_id,exam_score,exam_date)
select '二班java摸底考试' title,a.id stu_id, b.id sub_id,null exam_score,'2022-5-16' exam_date
from student_info a,subject_info b;

--至此，学生考试成绩的“模板”数据产生完成。
--学生不参加考试，分数为null。

--假设3，4号学生没有参加考试，都考试一个随机的分数。
select round(30+rand()*100,0)

--场景：假设学生已经有了分数，但3，4号学生没有参加考试。
update score_info set exam_score=round(30+rand()*100,0) where stu_id not in(3,4);

--现在有了成绩，就可以做报表 。
--基础报表：什么人，什么科目，多少分，考试时间
--有4个表，以哪个表为基础表 （select * from t1,t2,t3,t4... 第一个表t1就是基础表)
--明细表，就选择记录最“详细”的那个表。在此，就是成绩表。

--语法： select * from t1 a left join t2 b on a.id_xx=b.id_xx

select 
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,sub.subject_name
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
;        


--加上班级信息
select 
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,stu.class_id,cls.class_name,
sub.subject_name

from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
;    

--基础报表做完了。

--加：名次（每个班都有自己的第1次）
--思路：每个班分在一个区(partition)，每个区的分数倒排（高的在前面），最前面的是第1中。
--函数名称：分析函数：开窗函数。
-- RANK() over(partition by 班级 order by 分数 desc)

select 
DENSE_RANK() over(partition by stu.class_id order by score.exam_score desc) score_rank,
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,stu.class_id,cls.class_name,
sub.subject_name
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
;    

--标记：缺考
select 
DENSE_RANK() over(partition by stu.class_id order by score.exam_score desc) score_rank,
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,stu.class_id,cls.class_name,
sub.subject_name,
(case when exam_score is null then '缺考' else '正常' end) 标记
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
;    

--加：优秀（分数大于90分）
select 
DENSE_RANK() over(partition by stu.class_id order by score.exam_score desc) score_rank,
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,stu.class_id,cls.class_name,
sub.subject_name,
(case when exam_score is null then '缺考' else '' end) 标记,
(case when exam_score>90 then '优秀' else '' end ) 优秀
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
;    

--加行号
with src as 
(
select 
DENSE_RANK() over(partition by stu.class_id order by score.exam_score desc) score_rank,
score.stu_id,score.exam_score,score.exam_date,score.subject_id,
stu.name,stu.class_id,cls.class_name,
sub.subject_name,
(case when exam_score is null then '缺考' else '' end) 标记,
(case when exam_score>90 then '优秀' else '' end ) 优秀
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
)
select
row_number() over() r_number,
src.*,
now() 制表时间
from src
;    


--标题改为中文

with src as 
(
select 
DENSE_RANK() over(partition by stu.class_id order by score.exam_score desc) 名次,
score.stu_id 学号,score.exam_score 分数,score.exam_date 日期,score.subject_id 科目ID,
stu.name 姓名,stu.class_id 班级ID,cls.class_name 班级名称,
sub.subject_name 科目名称,
(case when exam_score is null then '缺考' else '' end) 标记,
(case when exam_score>90 then '优秀' else '' end ) 优秀
from score_info score left join student_info stu on score.stu_id=stu.id
                                          left join subject_info sub on score.subject_id=sub.id
                                            left join class_info cls on stu.class_id=cls.id
)
select
row_number() over() 行号,
src.*,
now() 制表时间
from src
;    

--SQL任务：建表，手工添加模拟数据，通过代码产生考试模拟数据。
--JAVA任务：产生csv字符串+加上文件下载的响应头，hbx得到一个链接，单击下载。