SELECT DISTINCT vs GROUP BY 在 MySQL 中?
SELECTDISTINCT可用于给出不同的值。使用它来删除重复记录,它也可以与聚合函数一起使用。例如:MAX、AVG等。这可以应用于单个列。
现在,我正在创建一个表以将SELECTDISTINCT用于列。在CREATE命令的帮助下创建表-
mysql> CREATE TABLE DistinctDemo -> ( -> id int, -> name varchar(100) -> );
插入记录-
mysql> INSERT into DistinctDemo values(1,'John'); mysql> INSERT into DistinctDemo values(2,'John'); mysql> INSERT into DistinctDemo values(3,'Bob'); mysql> INSERT into DistinctDemo values(4,'John'); mysql> INSERT into DistinctDemo values(5,'David'); mysql> INSERT into DistinctDemo values(6,'Bob');
在SELECT命令的帮助下显示所有记录。查询如下-
mysql> SELECT * from DistinctDemo;
执行上述查询后,我们将获得以下输出-
+------+-------+ | id | name | +------+-------+ | 1 | John | | 2 | John | | 3 | Bob | | 4 | John | | 5 | David | | 6 | Bob | +------+-------+ 6 rows in set (0.00 sec)
上面,我们有六个记录,其中John和Bob是重复的。我们可以应用DISTINCT来删除重复的记录。语法如下-
SELECT distinctcolumn_name from yourTableName order by column_name;
现在,我正在应用上述查询来删除重复项-
mysql> SELECT distinct name from DistinctDemo;
以下是输出
+-------+ | name | +-------+ | John | | Bob | | David | +-------+ 3 rows in set (0.00 sec)
Selectgroupby可用于从不同列中获取数据并将其分组为一列或多列。这也可以应用于聚合function.For示例:SUM、AVG等。
首先,我在CREATE命令的帮助下创建一个表-
mysql> CREATE table GroupDemo1 -> ( -> id int, -> name varchar(100), -> address varchar(100) -> );
创建表后,我将记录插入表中,如下所示-
mysql> INSERT into GroupDemo1 values(1,'John','US'); mysql> INSERT into GroupDemo1 values(2,'Bob','UK'); mysql> INSERT into GroupDemo1 values(3,'David','US'); mysql> INSERT into GroupDemo1 values(4,'David','US');
现在,我们可以在SELECT命令的帮助下显示所有记录-
mysql> SELECT * from GroupDemo1;
以下是输出-
+------+-------+---------+ | id | name | address | +------+-------+---------+ | 1 | John | US | | 2 | Bob | UK | | 3 | David | US | | 4 | David | US | +------+-------+---------+ 4 rows in set (0.00 sec)
我将应用GROUPBY来知道出现次数相同的地址。这是语法-
SELECT column_name1,......N aggregate function( ) from yourTableName group by column_name;
让我们将上述语法应用于GROUPBY-
mysql> SELECT address, count(*) from GroupDemo1 group by address;
以下是输出-
+---------+----------+ | address | count(*) | +---------+----------+ | US | 3 | | UK | 1 | +---------+----------+ 2 rows in set (0.00 sec)