需要在mysql数据库做一个表,类似这样,
对所有学科进行分类,语文用2表示,英语用4表示,数学用6表示,各科内容不变,其他学科依次延续,因为涉及学科太多,各位大神,如何快速批处理?求助想要这样的结果
需要在mysql数据库做一个表,类似这样,
原理:上下两个表全连接查询输出,如果需要可以将结果导入新表
create database csdn;
use csdn;
CREATE table A(
coursecatogory varchar(20),
coursename varchar(20)
);
CREATE table B(
weight int,
coursecatogory varchar(20)
);
insert into A VALUES('语文','文言文');
insert into A VALUES('语文','古诗');
insert into A VALUES('英语','阅读理解');
insert into A VALUES('英语','完形填空');
insert into A VALUES('数学','代数');
insert into A VALUES('数学','几何');
-- 用户可以指定
insert into B VALUES(2,'语文');
insert into B VALUES(4,'英语');
insert into B VALUES(6,'数学');
-- SELECT * FROM A;
-- SELECT * FROM B;
select b.weight as coursecatogory, a.coursename
from A a INNER JOIN B b
on a.coursecatogory = b. coursecatogory
ORDER BY B.weight ASC ;
-- 根据第二个表格给的数值排序,B表可以设置weight自增,不用手动输入权重
create database csdn;
use csdn;
CREATE table A(
coursecatogory varchar(20),
coursename varchar(20)
);
-- 插入数据,模拟数据库已有数据
insert into A VALUES('语文','文言文');
insert into A VALUES('语文','古诗');
insert into A VALUES('英语','阅读理解');
insert into A VALUES('英语','完形填空');
insert into A VALUES('数学','代数');
insert into A VALUES('数学','几何');
-- 构建辅助数据库
CREATE table B(
weight int UNIQUE AUTO_INCREMENT,
coursecatogory varchar(20)
);
insert into B(coursecatogory)
select DISTINCT coursecatogory
from A
-- 根据已有的学科名降序排序(这个不能根据用户指定数字了)
ORDER BY coursecatogory DeSC;
-- 查询结果
select 2*b.weight as coursecatogory, a.coursename
from A a INNER JOIN B b
on a.coursecatogory = b. coursecatogory
ORDER BY B.weight ASC ;
测试存储过程代码:
select @weight:=@weight + 2 as w, coursecatogory
from (select DISTINCT coursecatogory from A ORDER BY coursecatogory DeSC) C,
(select @weight:=0) D ;
这个和我们刚刚上面2种方法建立的B表存储内容格式一样
改进方法二后:
create database csdn;
use csdn;
CREATE table A(
coursecatogory varchar(20),
coursename varchar(20)
);
-- 插入数据,模拟数据库已有数据
insert into A VALUES('语文','文言文');
insert into A VALUES('语文','古诗');
insert into A VALUES('英语','阅读理解');
insert into A VALUES('英语','完形填空');
insert into A VALUES('数学','代数');
insert into A VALUES('数学','几何');
-- select 2*b.weight as coursecatogory, a.coursename
-- from A a INNER JOIN B b
-- on a.coursecatogory = b. coursecatogory
-- ORDER BY B.weight ASC ;
-- 根据已有的学科名降序排序(这个不能根据用户指定数字了)
-- 类似构建B表格
-- select @weight:=@weight + 2 as w, coursecatogory
-- from (select DISTINCT coursecatogory from A ORDER BY coursecatogory DeSC) C,
-- (select @weight:=0) D ;
select B.w as coursecatogory, a.coursename
from A a INNER JOIN
(
select @weight:=@weight + 2 as w, coursecatogory
from (select DISTINCT coursecatogory from A ORDER BY coursecatogory DeSC) C,
(select @weight:=0) D
)B
on a.coursecatogory = b. coursecatogory
ORDER BY B.w ASC ;