有如下表:
CREATE table score_rows(sname VARCHAR(50),class VARCHAR(50),chinese TINYINT,math TINYINT,english TINYINT);
INSERT into score_rows VALUES('a','二一班',70,80,90);
INSERT into score_rows VALUES('b','二一班',75,85,95);
INSERT into score_rows VALUES('c','二二班',70,90,90);
INSERT into score_rows VALUES('d','二三班',80,80,90);
INSERT into score_rows VALUES('e','三一班',90,80,90);
INSERT into score_rows VALUES('f','三二班',80,70,90);
INSERT into score_rows VALUES('g','三三班',90,80,90);
目标:以下实现方式不够灵活:(select sname,class,'chinese' as type,chinese as sore from score_rows)
union
(select sname,class,'math' as type,math as sore from score_rows)
union
(select sname,class,'english' as type,english as sore from score_rows) 请问有什么比较灵活的方式,可以在有课程数不固定的情况下实现?
CREATE table score_rows(sname VARCHAR(50),class VARCHAR(50),chinese TINYINT,math TINYINT,english TINYINT);
INSERT into score_rows VALUES('a','二一班',70,80,90);
INSERT into score_rows VALUES('b','二一班',75,85,95);
INSERT into score_rows VALUES('c','二二班',70,90,90);
INSERT into score_rows VALUES('d','二三班',80,80,90);
INSERT into score_rows VALUES('e','三一班',90,80,90);
INSERT into score_rows VALUES('f','三二班',80,70,90);
INSERT into score_rows VALUES('g','三三班',90,80,90);
目标:以下实现方式不够灵活:(select sname,class,'chinese' as type,chinese as sore from score_rows)
union
(select sname,class,'math' as type,math as sore from score_rows)
union
(select sname,class,'english' as type,english as sore from score_rows) 请问有什么比较灵活的方式,可以在有课程数不固定的情况下实现?
解决方案 »
- mysql全文检索结果为空
- 表a的数据导(不是简单导,要做过滤)到表b,可能有几万条数据.怎样保证最优性能,是在B/S下执行
- 请教:我的MySQL表出错不能访问
- 我用mysql的工具,不小心把数据库里的所有的表全部删除,有没有恢复的方法,
- vc中怎么样调用mysql的存储过程
- mysql里 SQLSTATE 01004的问题
- 如果把mysql的数据移植到sql server上呢?
- MySQL现在最新版支持中文的全文索引(中文的FULL TEXT)吗?
- mysql 的问题,求助!
- Mysql的自动备份工具?
- MYSQL cpu一直70%多,求助大神
- pt-table-checksum 无结果输出,日志提示此数据库为系统库,被过滤,请问如何解决
课程数不固定的,这种只能用动态sql来实现
学号 姓名 班级 ....
1 a 一班
2 b 二班
...................表B:科目表
科目ID 科目名称
1 chinese
2 english
3 math
..................表C:学习表对应哪个学生要学哪门课程
学号 科目ID
1 1
1 2
1 3
2 1
2 2
2 3
。表D: 成绩表
学号 科目号 成绩
1 1 70
1 2 80
1 3 90
2 1 60
2 2 100
2 3 90
.......这样一来,想要什么样的格式的数据都可以搞出来。而且科目可以任意加。
比如要想查询学生的成绩:
SELECT A.ID, A.NAME, C.NAME AS KM, D.SCORE FROM A LEFT JOIN D on A.XH=D.XH LEFT JOIN C on D.KM=C.KM;
DROP table IF EXISTS score_rows;
CREATE table score_rows(sname VARCHAR(50),class VARCHAR(50),chinese TINYINT,math TINYINT,english TINYINT);
INSERT into score_rows VALUES('a','二一班',70,80,90);
INSERT into score_rows VALUES('b','二一班',75,85,95);
INSERT into score_rows VALUES('c','二二班',70,90,90);
INSERT into score_rows VALUES('d','二三班',80,80,90);
INSERT into score_rows VALUES('e','三一班',90,80,90);
INSERT into score_rows VALUES('f','三二班',80,70,90);
INSERT into score_rows VALUES('g','三三班',90,80,90);select group_concat('select sname, class,', quote(column_name), ' as type,', column_name, ' as sore from score_rows' separator ' union ')
into @sql
from information_schema.columns
where table_schema=database() and table_name='score_rows'
and column_name not in('sname', 'class');
prepare st from @sql;
execute st;
deallocate prepare st;