MySQL單表查詢之分組查詢

標籤：mysql

分組查詢

 

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 * 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行。

本文出自 “自信蝸牛” 部落格，謝絕轉載！

MySQL單表查詢之分組查詢