mysql常见的列转行功能的思路分析

1.首先我们建张表

CREATE TABLE tbs (
years VARCHAR(30),
months VARCHAR(30),
counts INT
)

2.然后插入几行数据

INSERT INTO 
tbs(years,months,counts)
 VALUES	('2018','1',110),
	('2018','2',110),
	('2018','3',110),
	('2018','4',110),
	('2019','1',110),
	('2019','2',110),
	('2019','3',110),
	('2019','4',110)

3.查看表数据

SELECT * FROM tbs

结果如下

4.进行行转列

SELECT
  years,
  SUM(CASE months WHEN '1' THEN counts ELSE 0 END) AS '一月',
  SUM(CASE months WHEN '2' THEN counts ELSE 0 END) AS '二月',
  SUM(CASE months WHEN '3' THEN counts ELSE 0 END) AS '三月',
  SUM(CASE months WHEN '4' THEN counts ELSE 0 END) AS '四月'
FROM tbs
GROUP BY years

最后的结果如下