func_sqlfunctionGROUP__BY

GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SQL GROUP BY 语法


SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

演示数据库

在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：


+----+--------------+---------------------------+-------+---------+
+----+--------------+---------------------------+-------+---------+
<table>
<tr>
<th>1</th>
<th>Google</th>
<th>https://www.google.cm/</th>
<th>1</th>
<th>USA</th>
</tr>
<tr>
<td>3</td>
<td>菜鸟教程</td>
<td>http://www.runoob.com/</td>
<td>4689</td>
<td>CN</td>
</tr>
<tr>
<td>4</td>
<td>微博</td>
<td>http://weibo.com/</td>
<td>20</td>
<td>CN</td>
</tr>
<tr>
<td>5</td>
<td>Facebook</td>
<td>https://www.facebook.com/</td>
<td>3</td>
<td>USA</td>
</tr>
<tr>
<td>7</td>
<td>stackoverflow</td>
<td>http://stackoverflow.com/</td>
<td>0</td>
<td>IND</td>
</tr>
</table>

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

下面是 "access_log" 网站访问记录表的数据：


mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
+-----+---------+-------+------------+
<table>
<tr>
<th>1</th>
<th>1</th>
<th>45</th>
<th>2016-05-10</th>
</tr>
<tr>
<td>3</td>
<td>1</td>
<td>230</td>
<td>2016-05-14</td>
</tr>
<tr>
<td>4</td>
<td>2</td>
<td>10</td>
<td>2016-05-14</td>
</tr>
<tr>
<td>5</td>
<td>5</td>
<td>205</td>
<td>2016-05-14</td>
</tr>
<tr>
<td>6</td>
<td>4</td>
<td>13</td>
<td>2016-05-15</td>
</tr>
<tr>
<td>7</td>
<td>3</td>
<td>220</td>
<td>2016-05-15</td>
</tr>
<tr>
<td>8</td>
<td>5</td>
<td>545</td>
<td>2016-05-16</td>
</tr>
<tr>
<td>9</td>
<td>3</td>
<td>201</td>
<td>2016-05-17</td>
</tr>
</table>

+-----+---------+-------+------------+
9 rows in set (0.00 sec)

GROUP BY 简单应用

统计 access_log 各个 site_id 的访问量：

实例


SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

执行以上 SQL 输出结果如下：

![](https://www.runoob.com/wp-content/uploads/2013/09/groupby1.jpg)

SQL GROUP BY 多表连接

下面的 SQL 语句统计有记录的网站的记录数量：

实例


SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;