本文實例講述了MySQL橫縱表相互轉化操作實現(xiàn)方法。分享給大家供大家參考，具體如下：
先創(chuàng)建一個成績表（縱表）
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ù)學',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ù)學',100);
再創(chuàng)建一個成績表（橫表）
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 '數(shù)學' then score else 0 end)
as '數(shù)學', 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;
更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關技巧匯總》及《MySQL常用函數(shù)大匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
您可能感興趣的文章:- Mysql縱表轉換為橫表的方法及優(yōu)化教程
