本文實例講述了MySQL橫縱表相互轉化操作實現方法。分享給大家供大家參考,具體如下:
先創建一個成績表(縱表)
create table user_score ( name varchar(20), subjects varchar(20), score int ); insert into user_score(name,subjects,score) values('張三','語文',60); insert into user_score(name,subjects,score) values('張三','數學',70); insert into user_score(name,subjects,score) values('張三','英語',80); insert into user_score(name,subjects,score) values('李四','語文',90); insert into user_score(name,subjects,score) values('李四','數學',100);
再創建一個成績表(橫表)
create table user_score2 ( name varchar(20), yuwen int, shuxue int, yingyu int ); insert into user_score2(name,yuwen,shuxue,yingyu) values('張三',60,70,80); insert into user_score2(name,yuwen,shuxue,yingyu) values('李四',90,100,0);
縱錶轉橫表
select name,sum(case subjects when '語文' then score else 0 end) as '語文',sum(case subjects when '數學' then score else 0 end) as '數學', sum(case subjects when '英語' then score else 0 end) as '英語'from user_score group by name;
縱錶轉橫表
SELECT name,'yuwen' AS subjects,yuwen AS score FROM user_score2 UNION ALL SELECT name,'shuxue' AS subjects,shuxue AS score FROM user_score2 UNION ALL SELECT name,'yingyu' AS subjects,yingyu AS score FROM user_score2 ORDER BY name,subjects DESC;
[retouched ] MySQL橫縱表相互轉化操作實現方法已經有250次圍觀