How to realize the Transformation of horizontal and Vertical tables by MySQL
This article mainly explains how MySQL converts horizontal and vertical tables into each other, the content is clear, interested friends can learn, I believe it will be helpful after reading.
In this paper, an example is given to describe the realization method of mutual conversion operation of MySQL horizontal and vertical tables. Share with you for your reference, the details are as follows:
First create a score sheet (profile)
Create table user_score (name varchar (20), subjects varchar (20), score int); insert into user_score (name,subjects,score) values (Zhang San, Chinese, 60); insert into user_score (name,subjects,score) values (Zhang San, Mathematics, 70); insert into user_score (name,subjects,score) values (Zhang San, English, 80) Insert into user_score (name,subjects,score) values ('Li Si', 'Chinese', 90); insert into user_score (name,subjects,score) values ('Li Si', 'Mathematics', 100)
Create another score sheet (horizontal table)
Create table user_score2 (name varchar (20), yuwen int, shuxue int, yingyu int); insert into user_score2 (name,yuwen,shuxue,yingyu) values ('Zhang San', 60 mine70 and 80); insert into user_score2 (name,yuwen,shuxue,yingyu) values ('Li Si', 90 meme 100mem0)
Vertical table to horizontal table
Select name,sum (case subjects when 'Chinese' then score else 0 end) as' Chinese', sum (case subjects when 'then score else 0 end) as' math', sum (case subjects when 'then score else 0 end) as' English 'from user_score group by name
Vertical table to horizontal table
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
After reading the above content, do you have a further understanding of how MySQL can convert horizontal and vertical tables into each other? if you want to learn more, you are welcome to follow the industry information channel.
