横行数据转换成纵表数据

最新推荐文章于 2021-09-07 17:46:50 发布

坏小子小幺儿

最新推荐文章于 2021-09-07 17:46:50 发布

阅读量581

点赞数

分类专栏： mysql 文章标签： sql

本文链接：https://blog.csdn.net/qq_36970503/article/details/79439919

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

创建表(如图)

代码:CREATE TABLE `NewTable` (

`id` int(11) NOT NULL ,
`year` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`month` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0

;

输出为:

sql代码:

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 (
SELECT year,month, amount FROM tx
GROUP BY year,month
) AS A
GROUP BY year