一个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);

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