SQL 查询 分类统计

Question

示例表 1

year	month	price
2019	1	50
2019	2	30
2019	3	40
2019	4	70
2020	1	35
2020	2	35
2020	3	44
2020	4	67

/* Insert 语句 */
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2019', '1', 50);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2019', '2', 30);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2019', '3', 40);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2019', '4', 70);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2020', '1', 35);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2020', '2', 35);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2020', '3', 44);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2020', '4', 67);
INSERT INTO ``(`year`, `month`, `price`) VALUES ('2019', '4', 67);

分类统计

SELECT year,
	COUNT( `month` = '1' OR NULL) AS 'm1',
	COUNT( `month` = '2' OR NULL) AS 'm2',
	COUNT( `month` = '3' OR NULL) AS 'm3',
	COUNT( `month` = '4' OR NULL) AS 'm4',
price
FROM `表 1` GROUP BY `year`

结果

year	m1	m2	m3	m4	price
2019	1	1	1	2	50
2020	1	1	1	1	35

分类求和统计

/* 涉及行列如何互换 */
SELECT year,
    sum(case `month` when '1' then `price` else 0 end) as 'm1',
    sum(case `month` when '2' then `price` else 0 end) as  'm2',
    sum(case `month` when '3' then `price` else 0 end) as 'm3',
sum(price) as price_sum
FROM `表 1` GROUP BY `year`;

结果

year	m1	m2	m3	price_sum
2019	50	30	40	257
2020	35	35	44	181