Simple implementation of modifying rows of Mysql columns and displaying data, mysql rows of data

Simple implementation of modifying rows of Mysql columns and displaying data, mysql rows of data

Create a test table:

DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (`year` int(11) DEFAULT NULL,`month` int(11) DEFAULT NULL,`amount` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert data:

INSERT INTO `test` VALUES ('1991', '1', '1.1');INSERT INTO `test` VALUES ('1991', '2', '1.2');INSERT INTO `test` VALUES ('1991', '3', '1.3');INSERT INTO `test` VALUES ('1991', '4', '1.4');INSERT INTO `test` VALUES ('1992', '1', '2.1');INSERT INTO `test` VALUES ('1992', '2', '2.2');INSERT INTO `test` VALUES ('1992', '3', '2.3');INSERT INTO `test` VALUES ('1992', '4', '2.3');

Think about the question requirements:

Use SUM (IF () to generate a column + with rollup to generate a summary row, and use IFNULL to display the title of the summary row as Total_num.

Implementation

The SQL code block is as follows:

select year,sum(if(month=1,amount,0)) as "M1",sum(if(month=2,amount,0)) as "M2",sum(if(month=3,amount,0)) as "M3",sum(if(month=4,amount,0)) as "M4"from testGROUP by year;

The effect is as follows:

The above is a simple implementation of Mysql column modification and display data. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!