如何基于MySQL中特定月份的记录对选定的列值求和？
让我们首先创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, PurchaseDate date, SalePrice int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(PurchaseDate,SalePrice) values('2018-01-10',450);
mysql> insert into DemoTable(PurchaseDate,SalePrice) values('2019-12-25',1000);
mysql> insert into DemoTable(PurchaseDate,SalePrice) values('2016-12-02',5560);
mysql> insert into DemoTable(PurchaseDate,SalePrice) values('2015-02-20',4550);
mysql> insert into DemoTable(PurchaseDate,SalePrice) values('2015-12-11',4110);使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+--------------+-----------+ | Id | PurchaseDate | SalePrice | +----+--------------+-----------+ | 1 | 2018-01-10 | 450 | | 2 | 2019-12-25 | 1000 | | 3 | 2016-12-02 | 5560 | | 4 | 2015-02-20 | 4550 | | 5 | 2015-12-11 | 4110 | +----+--------------+-----------+ 5 rows in set (0.00 sec)
以下是根据特定月份对MySQL中选定的列值求和的查询。例如，此处仅在12月（即12月）为PurchaseDate添加了SalePrice-
mysql> select SUM(SalePrice) from DemoTable where month(PurchaseDate)=12;
上面仅添加了所选月份（即12月12日）的记录(SalePrice)-
2019-12-25 2016-12-02 2015-12-11
这将产生以下输出-
+----------------+ | SUM(SalePrice) | | 10670 | +----------------+ 1 row in set (0.00 sec)