1. 创建表:
create table tb(
ID int,
username varchar(20),
CN_SCORE float,
MATH_SCORE float,
EN_SCORE float
);
2. 插入数据:
INSERT INTO tb values(1,'lisi',87,65,75);
INSERT INTO tb values(2,'michale',78,95,81);
INSERT INTO tb values(3,'xiaocxiao',97,78,91);
INSERT INTO tb values(4,'zhangsan',80,55,75));
3. 补数据,添加个total:
alter table tb add column total float after username;
update tb set total=(CN_SCORE+MATH_SCORE+EN_SCORE) WHERE ID IN(1,2,3,4);
或下面的形式:
UPDATE test
SET name = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
4. 开始列转行:
select username, total, '语文' COURSE, CN_SCORE as SCORE from tb
union
select username, total,'数学' COURSE, MATH_SCORE as SCORE from tb
union
select username,total, '英语' COURSE, EN_SCORE as SCORE from tb
order by username,COURSE;
| ID | username | total | CN_SCORE | MATH_SCORE | EN_SCORE |
| 1 | lisi | 227 | 87 | 65 | 75 |
| 2 | michale | 254 | 78 | 95 | 81 |
| 3 | xiaoxiao | 266 | 97 | 78 | 91 |
| 4 | zhangsan | 210 | 80 | 55 | 75 |
转换后:
| username | total | COURSE | SCORE |
| lisi | 227 | 语文 | 87 |
| lisi | 227 | 英语 | 75 |
| lisi | 227 | 数学 | 65 |
| michale | 254 | 数学 | 95 |
| michale | 254 | 英语 | 81 |
| michale | 254 | 语文 | 78 |
| xiaoxiao | 266 | 语文 | 97 |
| xiaoxiao | 266 | 英语 | 91 |
| xiaoxiao | 266 | 数学 | 78 |
| zhangsan | 210 | 语文 | 80 |
| zhangsan | 210 | 英语 | 75 |
| zhangsan | 210 | 数学 | 55 |