来自相似日期记录的MySQL中的总和
DATE()为此使用GROUPBY。让我们首先创建一个表-
mysql> create table DemoTable1358
-> (
-> PurchaseDate datetime,
-> ProductPrice int
-> );使用insert命令在表中插入一些记录。在这里，我们插入了日期记录，其中一些记录的日期相似-
mysql> insert into DemoTable1358 values('2019-09-20 12:34:00', 450);
mysql> insert into DemoTable1358 values('2019-09-21 11:00:00', 1050);
mysql> insert into DemoTable1358 values('2018-09-21 02:10:00', 2050);
mysql> insert into DemoTable1358 values('2019-09-21 05:20:40', 5050);
mysql> insert into DemoTable1358 values('2016-09-21 04:10:56', 1000);使用select语句显示表中的所有记录-
mysql> select * from DemoTable1358;
这将产生以下输出-
+---------------------+--------------+ | PurchaseDate | ProductPrice | +---------------------+--------------+ | 2019-09-20 12:34:00 | 450 | | 2019-09-21 11:00:00 | 1050 | | 2018-09-21 02:10:00 | 2050 | | 2019-09-21 05:20:40 | 5050 | | 2016-09-21 04:10:56 | 1000 | +---------------------+--------------+ 5 rows in set (0.00 sec)
以下是对同一天的ProductPrice值求和的查询-
mysql> select PurchaseDate,sum(ProductPrice) from DemoTable1358
-> group by date(PurchaseDate);这将产生以下输出-
+---------------------+-------------------+ | PurchaseDate | sum(ProductPrice) | +---------------------+-------------------+ | 2019-09-20 12:34:00 | 450 | | 2019-09-21 11:00:00 | 6100 | | 2018-09-21 02:10:00 | 2050 | | 2016-09-21 04:10:56 | 1000 | +---------------------+-------------------+ 4 rows in set (0.00 sec)