How to convert MySQL horizontally to vertically and vertically to horizontally

How to convert MySQL horizontally to vertically and vertically to horizontally

Initialize Data

DROP TABLE IF EXISTS `test_01`;
CREATE TABLE `test_01` (
 `id` int(0) NOT NULL,
 `user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'User',
 `km` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Subject',
 `fs` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Score',
 `time` datetime(0) NULL DEFAULT NULL COMMENT 'time',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `test_01` VALUES (1, '小三', '语文', '98', '2020-08-06 15:51:21');
INSERT INTO `test_01` VALUES (2, '小三', '数学', '90', '2020-07-01 15:51:25');
INSERT INTO `test_01` VALUES (3, '小三', '英语', '77', '2020-06-01 15:51:28');
INSERT INTO `test_01` VALUES (4, '小等', '英语', '78', '2020-06-01 15:51:28');

1. Horizontal to vertical arrangement

SELECT 
	user,
	SUM( CASE WHEN km = "Chinese" THEN fs ELSE 0 END ) "Chinese",
	SUM( CASE WHEN km = "Mathematics" THEN fs ELSE 0 END ) "Mathematics",
	SUM( CASE WHEN km = "English" THEN fs ELSE 0 END ) "English" 
FROM
	test_01 
GROUP BY user

insert image description here

2. Vertical to horizontal arrangement

SELECT km FROM test_01 WHERE id = 1 
UNION 
SELECT fs FROM test_01 WHERE id = 1 

insert image description here

This is the end of this article about how to convert MySQL horizontally to vertically and vertically to horizontally. For more information about how to convert MySQL horizontally to vertically and vertically to horizontally, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL horizontal and vertical table conversion operation implementation method

<<:  Detailed tutorial on distributed operation of jmeter in docker environment

>>:  Implementation of fastdfs+nginx cluster construction

Recommend

Dockerfile implementation code when starting two processes in a docker container

I want to make a docker for cron scheduled tasks ...

Full process record of Nginx reverse proxy configuration

1. Preparation Install Tomcat on Linux system, us...

A Different Kind of "Cancel" Button

The “Cancel” button is not part of the necessary ...

Analyze the problem of pulling down the Oracle 11g image configuration in Docker

1. Pull the image docker pull registry.cn-hangzho...

Docker private warehouse harbor construction process

1. Preparation 1.1 harbor download harbor downloa...

Summary of some thoughts on binlog optimization in MYSQL

question Question 1: How to solve the performance...

How to modify server uuid in Mysql

Source of the problem: If the slave server is the...

Detailed tutorial on VMware installation of Linux CentOS 7.7 system

How to install Linux CentOS 7.7 system in Vmware,...

30 minutes to give you a comprehensive understanding of React Hooks

Table of contents Overview 1. useState 1.1 Three ...

Centos8 bridge static IP configuration method in VMware virtual machine

1. Make sure the network connection method is bri...

HTML CSS3 does not stretch the image display effect

1. Use the transform attribute to display the ima...

Things about installing Homebrew on Mac

Recently, Xiao Ming just bought a new Mac and wan...

Detailed explanation of using Baidu style in eslint in React project

1. Install Baidu Eslint Rule plugin npm i -D esli...

JS, CSS and HTML to implement the registration page

A registration page template implemented with HTM...