MySQL橫縱表相互轉(zhuǎn)化操作實(shí)現(xiàn)方法
本文實(shí)例講述了MySQL橫縱表相互轉(zhuǎn)化操作實(shí)現(xiàn)方法。分享給大家供大家參考,具體如下:
先創(chuàng)建一個(gè)成績表(縱表)
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è)成績表(橫表)
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ù)大匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。
相關(guān)文章:
1. 數(shù)據(jù)庫Oracle9i的企業(yè)管理器簡介2. MySQL 億級數(shù)據(jù)導(dǎo)入導(dǎo)出及遷移筆記3. Delphi中的Access技巧集4. navicat for mysql創(chuàng)建數(shù)據(jù)庫的方法5. SQL Server主鍵與外鍵設(shè)置以及相關(guān)理解6. 巧用SQL語言在ACCESS數(shù)據(jù)庫中批量替換內(nèi)容7. MySql遠(yuǎn)程連接的實(shí)現(xiàn)方法8. MySQL MyISAM 與InnoDB 的區(qū)別9. 快速解決mysql導(dǎo)數(shù)據(jù)時(shí),格式不對、導(dǎo)入慢、丟數(shù)據(jù)的問題10. MYSQL技巧:為現(xiàn)有字段添加自增屬性

網(wǎng)公網(wǎng)安備