MySQL 如何根据日期创建一个月份列，并显示相应列的总和，在其中发现重复日期

这可以使用MySQL中的DATE_FORMAT（）函数来实现。首先创建一个表 –

mysql> create table DemoTable
   -> (
   -> PurchaseDate date,
   -> Amount int
   -> );
Query OK, 0 rows affected (0.52 sec)

使用 insert 命令向表中插入一些记录 –

mysql> insert into DemoTable values('2019-10-12',500);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('2018-10-12',1000);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('2019-01-10',600);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('2018-10-12',600);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('2018-11-10',800);
Query OK, 1 row affected (0.18 sec)

使用 select 语句显示表中的所有记录 –

mysql> select *from DemoTable;

这将产生以下输出 –

+--------------+--------+
| PurchaseDate | Amount |
+--------------+--------+
|   2019-10-12 |    500 |
|   2018-10-12 |   1000 |
|   2019-01-10 |    600 |
|   2018-10-12 |    600 |
|   2018-11-10 |    800 |
+--------------+--------+
5 rows in set (0.00 sec)

以下是从日期创建月份列并显示相应列的总和的查询，其中发现重复日期 –

mysql> select sum(Amount) as Amount,date_format(PurchaseDate,'%b') AS Month from DemoTable
   -> group by date_format(PurchaseDate,'%Y-%m');

这将产生以下输出 –

+--------+-------+
| Amount | Month |
+--------+-------+
|    500 |   Oct |
|   1600 |   Oct |
|    600 |   Jan |
|    800 |   Nov |
+--------+-------+
4 rows in set (0.00 sec)