本文實(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('張三','語(yǔ)文',60);
insert into user_score(name,subjects,score) values('張三','數(shù)學(xué)',70);
insert into user_score(name,subjects,score) values('張三','英語(yǔ)',80);
insert into user_score(name,subjects,score) values('李四','語(yǔ)文',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 '語(yǔ)文' then score else 0 end)
as '語(yǔ)文',sum(case subjects when '數(shù)學(xué)' then score else 0 end)
as '數(shù)學(xué)', sum(case subjects when '英語(yǔ)' then score else 0 end)
as '英語(yǔ)'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ǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
您可能感興趣的文章:- Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程
