MySQL函数substring_index和GROUP_CONCAT实例

ID 学生ID 学生姓名 班级名称 年级名称  科目  成绩

# insert语句生成

for i in $(seq 9)
do
  echo "insert into SCORE values($((0+$i)),$((0+$i%9)),'S_$((0+$i%9))','class_1','grade_1','yuwen',$((61+$i)));"
  echo "insert into SCORE values($((10+$i)),$((0+$i%9)),'S_$((0+$i%9))','class_1','grade_1','shuxue',$((71+$i)));"
  echo "insert into SCORE values($((20+$i)),$((0+$i%9)),'S_$((0+$i%9))','class_1','grade_1','yingyu',$((81+$i)));"
  echo "insert into SCORE values($((30+$i)),$((10+$i%9)),'S_$((10+$i%9))','class_2','grade_1','yuwen',$((62+$i)));"
  echo "insert into SCORE values($((40+$i)),$((10+$i%9)),'S_$((10+$i%9))','class_2','grade_1','shuxue',$((72+$i)));"
  echo "insert into SCORE values($((50+$i)),$((10+$i%9)),'S_$((10+$i%9))','class_2','grade_1','yingyu',$((82+$i)));"
  echo "insert into SCORE values($((60+$i)),$((20+$i%9)),'S_$((20+$i%9))','class_1','grade_2','yuwen',$((63+$i)));"
  echo "insert into SCORE values($((70+$i)),$((20+$i%9)),'S_$((20+$i%9))','class_1','grade_2','shuxue',$((73+$i)));"
  echo "insert into SCORE values($((80+$i)),$((20+$i%9)),'S_$((20+$i%9))','class_1','grade_2','yingyu',$((83+$i)));"
  echo "insert into SCORE values($((90+$i)),$((30+$i%9)),'S_$((30+$i%9))','class_1','grade_2','yuwen',$((64+$i)));"
  echo "insert into SCORE values($((100+$i)),$((30+$i%9)),'S_$((30+$i%9))','class_1','grade_2','shuxue',$((74+$i)));"
  echo "insert into SCORE values($((110+$i)),$((30+$i%9)),'S_$((30+$i%9))','class_1','grade_2','yingyu',$((84+$i)));"
done>insert.sql.txt

# 建表装入数据
create database student;
use student;
CREATE TABLE `SCORE` (
  `ID`  int(20) NOT NULL comment 'ID',
  `SID` int(20) NOT NULL comment '学生ID',
  `S_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL comment '学生姓名',
  `C_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL comment '班级名称',
  `G_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL comment '年级名称',
  `SUBJECT` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL comment '科目',
  `SCORE` int(20) NOT NULL comment '成绩',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 需求 1.求每个年级每个班级前五名学生（语数外总和）:
select ss.G_NAME,ss.C_NAME,
       substring_index(GROUP_CONCAT(ss.S_NAME order by ss.sum_score desc),',',5)
  from (select s.G_NAME,s.C_NAME,s.S_NAME,sum(s.SCORE) as sum_score
          from SCORE s 
         where s.SUBJECT in ('yuwen','shuxue','yingyu') 
         group by s.G_NAME,s.C_NAME,s.S_NAME) ss
group by ss.G_NAME,ss.C_NAME;


# 推演1：
select s.G_NAME,s.C_NAME,s.S_NAME,sum(s.SCORE) as sum_score
  from SCORE s 
 where s.SUBJECT in ('yuwen','shuxue','yingyu') 
 group by s.G_NAME,s.C_NAME,s.S_NAME;
根据年纪和班级以及学生分组，做语数外三门科目的总分
即求出每个同学的语数外总分，然后展示


# 推演2：
select ss.G_NAME,ss.C_NAME,GROUP_CONCAT(ss.S_NAME order by ss.sum_score desc)
  from (select s.G_NAME,s.C_NAME,s.S_NAME,sum(s.SCORE) as sum_score
          from SCORE s 
         where s.SUBJECT in ('yuwen','shuxue','yingyu') 
         group by s.G_NAME,s.C_NAME,s.S_NAME) ss
group by ss.G_NAME,ss.C_NAME;
查出的结果，根据年纪和班级分组，
然后根据每个年级的每个班级的学生的总成绩
由小到大排序，并且将排序之后学生姓名聚合成一列
使用GROUP_CONCAT(order by) group by 语法实现


# 推演3：
select ss.G_NAME,ss.C_NAME,SUBSTRING_INDEX(GROUP_CONCAT(ss.S_NAME order by ss.sum_score desc),',',5)
  from (select s.G_NAME,s.C_NAME,s.S_NAME,sum(s.SCORE) as sum_score
          from SCORE s 
         where s.SUBJECT in ('yuwen','shuxue','yingyu') 
         group by s.G_NAME,s.C_NAME,s.S_NAME) ss
group by ss.G_NAME,ss.C_NAME;
使用SUBSTRING_INDEX，指定分割符为逗号，取出前5组
得到前5名学生信息

# 需求2.各个科目的各个年级的平均分

select G_NAME,SUBJECT,avg(SCORE)
from SCORE 
group by G_NAME,SUBJECT;

# 需求3.各个科目的各个班级的平均分 select G_NAME,C_NAME,SUBJECT,avg(SCORE)
from SCORE 
group by  G_NAME,C_NAME,SUBJECT;

若泽大数据交流群：671914634