根据月份从MySQL表中选择总计
为此，您可以使用GROUPBYMONTH()。让我们首先创建一个表-
mysql> create table DemoTable1628
-> (
-> PurchaseDate date,
-> Amount int
-> );使用insert命令在表中插入一些记录。
mysql> insert into DemoTable1628 values('2019-01-10',1500);
mysql> insert into DemoTable1628 values('2019-10-10',2000);
mysql> insert into DemoTable1628 values('2019-10-24',100);
mysql> insert into DemoTable1628 values('2019-11-10',500);
mysql> insert into DemoTable1628 values('2019-12-10',1600);
mysql> insert into DemoTable1628 values('2019-10-10',900);使用select语句显示表中的所有记录-
mysql> select * from DemoTable1628;
这将产生以下输出-
+--------------+--------+ | PurchaseDate | Amount | +--------------+--------+ | 2019-01-10 | 1500 | | 2019-10-10 | 2000 | | 2019-10-24 | 100 | | 2019-11-10 | 500 | | 2019-12-10 | 1600 | | 2019-10-10 | 900 | +--------------+--------+ 6 rows in set (0.00 sec)
这是基于月份从数据库表中获取总计的查询-
mysql> select month(PurchaseDate) as Month,year(PurchaseDate) as Year,SUM(Amount)
-> from DemoTable1628
-> group by month(PurchaseDate);这将产生以下输出-
+-------+------+-------------+ | Month | Year | SUM(Amount) | +-------+------+-------------+ | 1 | 2019 | 1500 | | 10 | 2019 | 3000 | | 11 | 2019 | 500 | | 12 | 2019 | 1600 | +-------+------+-------------+ 4 rows in set (0.20 sec)