一些说明
在 MySQL 中通过 GROUP BY 进行分组查询，只会在每个组中出现一条数据。这一条数据并不是从改组中随机抽取的，而是该组结果的第一条数据。
表结构
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> DESC PRODUCT_ORDER;
+
| Field | Type | Null | Key | Default | Extra |
+
| ID | varchar(255) | NO | PRI | NULL | |
| PRODUCT_CODE | varchar(20) | YES | | NULL | |
| PRODUCT_PRICE | decimal(10,2) | YES | | NULL | |
| PRODUCT_COUNT | varchar(255) | YES | | NULL | |
| CREATE_DATE | date | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
+
6 rows in set
|
表数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| mysql> SELECT * FROM PRODUCT_ORDER;
+
| ID | PRODUCT_CODE | PRODUCT_PRICE | PRODUCT_COUNT | CREATE_DATE | CREATE_TIME |
+
| 312d9a34cee332176b3aae30 | SN | 106.18 | 8 | 2017-07-01 | 2017-07-01 16:12:09 |
| 40fb7a2feb0a8c4172bf7db1 | NJY | 122.51 | 8 | 2017-07-01 | 2017-07-01 09:32:33 |
| 4feac4f4d90958b26aa9ae7d | HWCX | 131.07 | 3 | 2017-07-04 | 2017-07-04 05:10:35 |
| 667a753557f7af9409632302 | RYCW | 97.44 | 1 | 2017-07-01 | 2017-07-01 05:11:14 |
| 66a4abaf56c46334f5fd481d | DDYX | 144 | 7 | 2017-07-02 | 2017-07-02 10:24:24 |
| 969c63845233437a98a0f617 | LSWXTD | 149.08 | 3 | 2017-07-02 | 2017-07-02 22:16:28 |
| a85ffc7a8982389f014b0a73 | DKX | 76.13 | 9 | 2017-07-05 | 2017-07-05 17:57:32 |
| d2230635b2a9b9e97479d576 | XM | 92.75 | 10 | 2017-07-04 | 2017-07-04 19:58:31 |
| d2ebe8d4366fc4fe3e756eb6 | MJSDJQR | 98 | 10 | 2017-07-03 | 2017-07-03 12:56:36 |
| d4a32009bdfbc38ab325f540 | ZNSH | 61.76 | 2 | 2017-07-02 | 2017-07-02 04:47:28 |
+
|
统计每天的订单数量（分组统计）
1
2
3
4
5
6
7
8
9
10
11
| mysql> SELECT CREATE_TIME, COUNT(*) FROM PRODUCT_ORDER GROUP BY CREATE_DATE;
+
| CREATE_TIME | COUNT(*) |
+
| 2017-07-01 16:12:09 | 3 |
| 2017-07-02 10:24:24 | 3 |
| 2017-07-03 12:56:36 | 1 |
| 2017-07-04 05:10:35 | 2 |
| 2017-07-05 17:57:32 | 1 |
+
5 rows in set
|
每天的第一笔订单（分组排序）
1
2
3
4
5
6
7
8
9
10
11
| mysql> SELECT * FROM (SELECT * FROM PRODUCT_ORDER ORDER BY CREATE_TIME DESC) PO GROUP BY CREATE_DATE;
+
| ID | PRODUCT_CODE | PRODUCT_PRICE | PRODUCT_COUNT | CREATE_DATE | CREATE_TIME |
+
| 312d9a34cee332176b3aae30 | SN | 106.18 | 8 | 2017-07-01 | 2017-07-01 16:12:09 |
| 969c63845233437a98a0f617 | LSWXTD | 149.08 | 3 | 2017-07-02 | 2017-07-02 22:16:28 |
| d2ebe8d4366fc4fe3e756eb6 | MJSDJQR | 98 | 10 | 2017-07-03 | 2017-07-03 12:56:36 |
| d2230635b2a9b9e97479d576 | XM | 92.75 | 10 | 2017-07-04 | 2017-07-04 19:58:31 |
| a85ffc7a8982389f014b0a73 | DKX | 76.13 | 9 | 2017-07-05 | 2017-07-05 17:57:32 |
+
5 rows in set
|
每天的前两笔订单（分组查询取前 N 条）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> SELECT * FROM PRODUCT_ORDER A
-> WHERE 2 > (SELECT COUNT(*) FROM PRODUCT_ORDER B WHERE A.ID > B.ID AND A.CREATE_DATE = B.CREATE_DATE)
-> ORDER BY CREATE_TIME DESC;
+
| ID | PRODUCT_CODE | PRODUCT_PRICE | PRODUCT_COUNT | CREATE_DATE | CREATE_TIME |
+
| a85ffc7a8982389f014b0a73 | DKX | 76.13 | 9 | 2017-07-05 | 2017-07-05 17:57:32 |
| d2230635b2a9b9e97479d576 | XM | 92.75 | 10 | 2017-07-04 | 2017-07-04 19:58:31 |
| 4feac4f4d90958b26aa9ae7d | HWCX | 131.07 | 3 | 2017-07-04 | 2017-07-04 05:10:35 |
| d2ebe8d4366fc4fe3e756eb6 | MJSDJQR | 98 | 10 | 2017-07-03 | 2017-07-03 12:56:36 |
| 969c63845233437a98a0f617 | LSWXTD | 149.08 | 3 | 2017-07-02 | 2017-07-02 22:16:28 |
| 66a4abaf56c46334f5fd481d | DDYX | 144 | 7 | 2017-07-02 | 2017-07-02 10:24:24 |
| 312d9a34cee332176b3aae30 | SN | 106.18 | 8 | 2017-07-01 | 2017-07-01 16:12:09 |
| 40fb7a2feb0a8c4172bf7db1 | NJY | 122.51 | 8 | 2017-07-01 | 2017-07-01 09:32:33 |
+
8 rows in set
|