成绩归类，排名

最新推荐文章于 2025-11-01 19:53:35 发布

原创最新推荐文章于 2025-11-01 19:53:35 发布 · 124 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种使用SQL创建表格并进行数据处理的方法，通过从源表cjrank中选择特定科目代码的数据，创建新的表格cj641和cj642，并基于这些表格创建了排名表rcj641和rcj642。最后，更新了cj641和cj642表中的排名字段，使其与排名表中的ID相对应。

CREATE TABLE cj641(
rank MEDIUMINT(8),
zkzh VARCHAR(10),
kmmc VARCHAR(10),
kmdm VARCHAR(10),
cj INT(10)
) SELECT rank,zkzh,kmmc,kmdm,cj FROM cjrank WHERE kmdm='641';

create table rcj641(id mediumint(8) auto_increment primary key,cj INT(10)) select cj from cj641 group by cj order by cj DESC;

UPDATE cj641 SET cj641.rank = (SELECT rcj641.id FROM rcj641 WHERE cj641.cj = rcj641.cj);

CREATE TABLE cj642(
rank MEDIUMINT(8),
zkzh VARCHAR(10),
kmmc VARCHAR(10),
kmdm VARCHAR(10),
cj INT(10)
) SELECT rank,zkzh,kmmc,kmdm,cj FROM cjrank WHERE kmdm='642';

create table rcj642(id mediumint(8) auto_increment primary key,cj INT(10)) select cj from cj642 group by cj order by cj DESC;

UPDATE cj642 SET cj642.rank = (SELECT rcj642.id FROM rcj642 WHERE cj642.cj = rcj642.cj);