如何在sql中groupby和count工作 [英] how does the groupby and count work in sql

问题描述

1> 选择浏览器，通过浏览器从logtest组计数（*）;

 code> + ----------- + ---------- + 
 |浏览器| count（*）| 
 + ----------- + ---------- + 
 | Firefox 3 | 14 | 
 |未知| 11 | 
 + ----------- + ---------- + 
  

集合中有2行

2> 选择浏览器，通过浏览器从logtest组计数c $ c>

  + ----------- + --------- ------- + 
 |浏览器| count（browser）| 
 + ----------- + ---------------- + 
 | Firefox 3 | 14 | 
 |未知| 11 | 
 + ----------- + ---------------- + 
  

集合中的2行

3> 从logtest中选择浏览器，

  + ----------- + ------ ---------- + 
 |浏览器| count（browser）| 
 + ----------- + ---------------- + 
 | Firefox 3 | 25 | 
 + ----------- + ---------------- + 
  

集合中的1行

为什么查询方式1>和2>导致相同的结果？在计数（*）和计数（somefiled）之间没有什么区别？

另外，查询2>和3>会导致不同的结果， strong> groupby 如此神奇？它如何工作？

---

更新：
我使用MySQL5.1。 ：）

解决方案

选择在关系上给你一个结果集。如果您按字段对选择进行分组，则结果集的行将按该字段分组，结果集的每一行将特定于结果组。

例如，您有一个名为Animals的表，其中包含以下字段：

 性别|名称
  

如果运行此查询（例如，在MySQL中）：

 选择类型，性别，来自动物的名称，其中类型<> 'pig'
  

你会得到不是'Pig'的所有动物。如果一行有Type ='pig'，它将被包含到结果中。

此查询：

 按类型从动物组中选择类型，性别，计数（*），性别
  

将有这么多行：类型数量*性别数量

您可以使用MySQL中的having子句创建条件。 p>

阅读更多此处

count（*）和 count（browser）是第一个将返回所有记录的数量，第二个将返回所有记录的数量 not（browser is null）。

尝试插入浏览器为空的行，然后运行1）和2）是最好的测试。

1> select browser,count(*) from logtest group by browser;

+-----------+----------+
| browser   | count(*) |
+-----------+----------+
| Firefox 3 |       14 |
| Unknown   |       11 |
+-----------+----------+

2 rows in set

2> select browser,count(browser) from logtest group by browser;

+-----------+----------------+
| browser   | count(browser) |
+-----------+----------------+
| Firefox 3 |             14 |
| Unknown   |             11 |
+-----------+----------------+

2 rows in set

3> select browser,count(browser) from logtest;

+-----------+----------------+
| browser   | count(browser) |
+-----------+----------------+
| Firefox 3 |             25 |
+-----------+----------------+

1 row in set

Why the query manner 1> and 2> result in the same result? Is there nothing difference between the count(*) and count(somefiled)?

Also,whay the query 2> and 3> result in the different result,why the groupby so magic? How does it work?

---

UPDATE: I am using MySQL5.1. :)

解决方案

A selection relationally gives you a result set. If you are grouping your selection by a field, the rows of the result set will be grouped by that field and each row of the result set will be specific for the group of the results.

For example you have a table named Animals with the following fields:

Type | Gender | Name

If you are running this query (in MySQL, for example):

 select Type, Gender, Name from Animals where Type <> 'Pig'

you'll get all the animals which are not 'Pig'. If a row has Type = 'pig', it will be included into the results.

This query:

select Type, Gender, count(*) from Animals group by Type, Gender

will have this many rows: number of types * number of genders

You can make conditions for your group by's using the having clause in MySQL.

Read more here

The difference between count(*) and count(browser) is that the first will return the number of all records, the second will return the number of all records where not (browser is null).

Try inserting a row where browser is null and then run 1) and 2), this is the best test.

这篇关于如何在sql中groupby和count工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！