一个sql题目, 统计每年每月的信息

1 题目描述

2 建表和插入数据

CREATE TABLE sales(
  id INT AUTO_INCREMENT PRIMARY KEY,
  YEAR VARCHAR(10),
  MONTH VARCHAR(10),
  amount FLOAT(2,1)
);

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

3 select语句

SELECT s.year ,
(SELECT t.amount FROM  sales AS t WHERE t.month = '1' AND s.year = t.year) AS m1,
(SELECT t.amount FROM  sales AS t WHERE t.month = '2' AND s.year = t.year) AS m2,
(SELECT t.amount FROM  sales AS t WHERE t.month = '3' AND s.year = t.year) AS m3,
(SELECT t.amount FROM  sales AS t WHERE t.month = '4' AND s.year = t.year) AS m4
FROM sales AS s GROUP BY s.year