本文實(shí)例講述了MySQL橫縱表相互轉(zhuǎn)化操作實(shí)現(xiàn)方法。分享給大家供大家參考，具體如下：

先創(chuàng)建一個(gè)成績(jī)表（縱表）

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(’張三’,’數(shù)學(xué)’,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(’李四’,’數(shù)學(xué)’,100);

再創(chuàng)建一個(gè)成績(jī)表（橫表）

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);

縱表轉(zhuǎn)橫表

select name,sum(case subjects when ’語文’ then score else 0 end) as ’語文’,sum(case subjects when ’數(shù)學(xué)’ then score else 0 end) as ’數(shù)學(xué)’, sum(case subjects when ’英語’ then score else 0 end) as ’英語’from user_score group by name;

縱表轉(zhuǎn)橫表

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;

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。