分组查询

 

mysql> select countrycode as total fromCity where id<10;

+-------+

| total |

+-------+

| AFG  |

| AFG  |

| AFG  |

| AFG  |

| NLD  |

| NLD  |

| NLD  |

| NLD  |

| NLD  |

+-------+

9 rows in set (0.00 sec)

 

mysql> select countrycode,count(*) astotal from City where id<10 group by countrycode;

+-------------+-------+

| countrycode | total |

+-------------+-------+

| AFG         |    4 |

| NLD         |    5 |

+-------------+-------+

2 rows in set (0.00 sec)

 

把相同的字段进行分组，并对分组内的数据进行统计。

 

 

 

使用having过滤分组

 

Having用于分组之后过滤数据，where用于分组之前选择数据。

 

mysql> select countrycode,count(*) astotal from City where id<101 group by countrycode;

+-------------+-------+

| countrycode | total |

+-------------+-------+

| AFG         |    4 |

| AGO         |    5 |

| AIA         |    2 |

| ALB         |    1 |

| AND         |    1 |

| ANT         |    1 |

| ARE         |    5 |

| ARG         |   32 |

| ASM         |    2 |

| ATG         |    1 |

| DZA         |   18 |

| NLD         |   28 |

+-------------+-------+

12 rows in set (0.01 sec)

 

mysql> select countrycode,count(*) astotal from City where id<101 group by countrycode having count(*)>10;

+-------------+-------+

| countrycode | total |

+-------------+-------+

| ARG         |   32 |

| DZA         |   18 |

| NLD         |   28 |

+-------------+-------+

3 rows in set (0.00 sec)

 

先选择数据，然后分组，然后having过滤数据。

 

 

在group by后求和

mysql> select countrycode,count(*) astotal from City where id<10 group by countrycode with rollup;

+-------------+-------+

| countrycode | total |

+-------------+-------+

| AFG         |    4 |

| NLD         |    5 |

| NULL        |    9 |

+-------------+-------+

3 rows in set (0.00 sec)

 

在最后增加一行显示求和结果。

 

 

多字段分组

 

先按照第一个字段进行分组，按照分组内容进行第二个字段的分组。

 

mysql> select countrycode,district fromCity where id<10;

+-------------+---------------+

| countrycode | district      |

+-------------+---------------+

| AFG         | Kabol         |

| AFG         | Qandahar      |

| AFG         | Herat         |

| AFG         | Balkh         |

| NLD         | Noord-Holland |

| NLD         | Zuid-Holland  |

| NLD         | Zuid-Holland  |

| NLD         | Utrecht       |

| NLD         | Noord-Brabant |

+-------------+---------------+

9 rows in set (0.00 sec)

 

mysql> select countrycode,district fromCity where id<10 group by countrycode,district;

+-------------+---------------+

| countrycode | district      |

+-------------+---------------+

| AFG         | Balkh         |

| AFG         | Herat         |

| AFG        | Kabol         |

| AFG         | Qandahar      |

| NLD         | Noord-Brabant |

| NLD         | Noord-Holland |

| NLD         | Utrecht       |

| NLD         | Zuid-Holland  |

+-------------+---------------+

8 rows in set (0.00 sec)

 

注：这里有一个知识点，5.7里sql_mode=only_full_group_by是默认打开的，不允许下面这样查询。

select * from City where id<10 group by countrycode,district;  列名countrycode,district需要在select后写明。除了countrycode,district，多余的列名出现会报错。

 

mysql5.6开启方法

mysql> setsql_mode=concat('ONLY_FULL_GROUP_BY,', @@sql_mode );

Query OK, 0 rows affected (0.00 sec)

 

 

限制查询结果行数量

 

mysql> select * from City limit 3;

+----+----------+-------------+----------+------------+

| ID | Name     | CountryCode | District | Population |

+----+----------+-------------+----------+------------+

|  1| Kabul    | AFG         | Kabol    |   1780000 |

|  2| Qandahar | AFG         | Qandahar |     237500 |

|  3| Herat    | AFG         | Herat    |    186800 |

+----+----------+-------------+----------+------------+

3 rows in set (0.00 sec)

 

前3行

 

mysql> select * from City limit 10,10;

+----+-------------------+-------------+---------------+------------+

| ID | Name              | CountryCode | District      | Population |

+----+-------------------+-------------+---------------+------------+

| 11 | Groningen         | NLD         | Groningen     |    172701 |

| 12 | Breda             | NLD         | Noord-Brabant |     160398 |

| 13 | Apeldoorn         | NLD         | Gelderland    |    153491 |

| 14 | Nijmegen          | NLD         | Gelderland    |    152463 |

| 15 | Enschede          | NLD         | Overijssel    |    149544 |

| 16 | Haarlem           | NLD         | Noord-Holland |     148772 |

| 17 | Almere            | NLD         | Flevoland     |    142465 |

| 18 | Arnhem            | NLD         | Gelderland    |    138020 |

| 19 | Zaanstad          | NLD         | Noord-Holland |     135621 |

| 20 | 麓s-Hertogenbosch  | NLD        | Noord-Brabant |     129170 |

+----+-------------------+-------------+---------------+------------+

10 rows in set (0.00 sec)

 

从10开始往后的10行。