在MySQL表中按月选择并过滤记录？
您可以将聚合函数SUM()与GROUPBY子句配合使用来实现此目的。
让我们创建一个表。创建表的查询如下-
mysql> create table SelectPerMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Price int, -> PurchaseDate datetime -> );
示例
使用插入命令在表中插入一些记录，其中之一将是购买日期。查询如下-
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(600,date_add(now(), interval -1 month)); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(600,date_add(now(), interval 2 month)); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(400,now()); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(800,date_add(now(), interval 3 month)); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(900,date_add(now(), interval 4 month)); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(100,date_add(now(), interval 4 month)); mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(1200,date_add(now(), interval -1 month));
使用select语句显示表中的所有记录。查询如下-
mysql> select *from SelectPerMonthDemo;
输出结果
以下是显示产品价格和购买日期的输出-
+----+-------+---------------------+ | Id | Price | PurchaseDate | +----+-------+---------------------+ | 1 | 600 | 2019-01-10 22:39:30 | | 2 | 600 | 2019-04-10 22:39:47 | | 3 | 400 | 2019-02-10 22:40:03 | | 4 | 800 | 2019-05-10 22:40:18 | | 5 | 900 | 2019-06-10 22:40:29 | | 6 | 100 | 2019-06-10 22:40:41 | | 7 | 1200 | 2019-01-10 22:40:50 | +----+-------+---------------------+ 7 rows in set (0.00 sec)
这是查询以根据购买日期根据各个月份获取记录-
mysql> select monthname(PurchaseDate) as MONTHNAME,sum(Price) from SelectPerMonthDemo -> group by monthname(PurchaseDate);
输出结果
+-----------+------------+ | MONTHNAME | sum(Price) | +-----------+------------+ | January | 1800 | | April | 600 | | February | 400 | | May | 800 | | June | 1000 | +-----------+------------+ 5 rows in set (0.07 sec)
如果您不想要月份名称（仅月份号），则使用以下查询-
mysql> select month(PurchaseDate),sum(Price) from SelectPerMonthDemo -> group by month(PurchaseDate);
输出结果
+---------------------+------------+ | month(PurchaseDate) | sum(Price) | +---------------------+------------+ | 1 | 1800 | | 4 | 600 | | 2 | 400 | | 5 | 800 | | 6 | 1000 | +---------------------+------------+ 5 rows in set (0.00 sec)