复杂的分组查询

作者: Vincent_Jiang | 来源:发表于2017-07-10 11:27 被阅读23次

SQL查询语句1
复杂的分组查询
Mysql笔记之分组函数和分组查询
java基础-day31-数据库2.0
SQL进阶部分一
MySQL——分组函数、distinct、分组查询、连接查询、子
SQL-分组查询
数据库分组查询(Group by)
分组筛选的异同
数据库高级查询2

一些说明

在 MySQL 中通过 GROUP BY 进行分组查询，只会在每个组中出现一条数据。这一条数据并不是从改组中随机抽取的，而是该组结果的第一条数据。

表结构

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

表数据

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 |
+--------------------------+--------------+---------------+---------------+-------------+---------------------+

统计每天的订单数量（分组统计）

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

每天的第一笔订单（分组排序）

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 条）

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