如何通过SQL语句构建个人云图的数据底层结构？

本文共计991个文字，预计阅读时间需要4分钟。

“截图+前端传入id为varchar类型的id值，考虑到17级学生的学号为11位，后续年份的学生的学号可能会突破int值的限制，所以使用varchar类型+1。t_certificate+证书表，由id查询四六级成绩，查询条件为“

截图

前端传入id varchar类型的id值，考虑到17级学生学号为11位，后续年级的学生的学号可能会突破int值的限制，所以使用varchar类型

1、t_certificate 证书表，由id查四六级成绩，查询条件为

（1）成绩>425的四六级证书

select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "1220106204" and

ce_name like "外语大学英语_级CET_" and ce_score>=425 ;

（2）成绩为合格的证书

select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "13200212108" and

ce_score = "合格" ;

（3）成绩为及格的证书

select ce_student_id as 学号,ce_student_name as 学生姓名,

ce_name as 考试科目,ce_score as 考试成绩

from t_certificate

where ce_student_id = "14200119104" and

ce_score = "及格" ;

2、t_competition 竞赛表

考虑到部分数据没有id,应该先从学生基本表中查名字，再由名字查竞赛名称

竞赛名称=co_name+co_level+co_awards 以第一条为例，则是大学生集成电路设计大赛国家级三等奖

select st_name as 学生姓名,

concat_ws('',co_name,co_level,co_awards) as 奖项

from t_competition,t_student

where co_student_name like

'%'|| (select st_name

from t_student

where st_id = "14200107126")||'%' ;

3、t_excellent_student 按照id查

单项名称为ex_year+ex_honor

select ex_student_name as 姓名,

ex_student_id as 学号,

concat_ws('',ex_year,'年',ex_honor) as 荣誉

from t_excellent_student

where ex_student_id = "14200141111" ;

4、t_innovation_train 创新训练表，俗称科研立项表

按照名字来查，使用模糊查询

单项为in_year+in_category+in_name

select t_student.st_name,

concat_ws('',in_year,'年',in_category,':',in_name) as 科研项目

from t_innovation_train,t_student

where in_students like

'%'|| (select st_name

from t_student

where st_id = "14200135118")||'%' ;

5、t_party_member 党员表

按名字来查

单项为pa_state+“党员”

select pa_student_name as 学生姓名,

concat_ws('',pa_state,'党员') as 党员状态

from t_party_member

where pa_student_name="杨磊";

6、t_post_graduate 考研表

按照名字来查

单项为 po_new_school+“研究生”

select po_student_name as 学生姓名,

concat_ws('',po_new_school,'研究生') as 研究生状况

from t_post_graduate

where po_student_name="何勇";

7、t_push_excellent 推优表

按照名字来查，若查询到数据

单项为2018.6.14+“已被推优”

select pu_student_name as 学生姓名,

concat_ws('',pu_time,'已被推优') as 推优状况

from t_push_excellent

where pu_student_name="王辉";

8、t_scholarship 奖学金表

按照学号来查。可能一个同学不止一条数据

单项为sc_year+“年获得”+sc_award

如果sc_award为三个字则sc_award=sc_award+”学金”

否则 sc_award=sc_award

备注:返回字符串类型的数组

select sc_student_name as 学生姓名,

concat_ws('',sc_year,'年获得',sc_awards,'学金') as 荣誉

from t_scholarship

where sc_awards like "___"

and sc_student_id = "16200143130"

union

select sc_student_name as 学生姓名,

concat_ws('',sc_year,'年获得',sc_awards) as 荣誉

from t_scholarship

where sc_awards like "____%" and

sc_student_id = "16200143130";