SELECTDISTINCT column1,column2 FROM table_name WHERE where_conditions; mysql> selectdistinct sex,age from student; +--------+------+ | sex | age | +--------+------+ | male | 10 | | female | 12 | | male | 11 | | male | NULL | | female | 11 | +--------+------+ 5 rows in set (0.02 sec)
group by 的使用
对于基础去重来说,group by的使用和distinct类似。
单列去重
语法:
SELECTcolumnsFROM table_name WHERE where_conditions GROUPBYcolumns;
执行:
mysql> select age from student groupby age; +------+ | age | +------+ | 10 | | 12 | | 11 | | NULL | +------+ 4 rows in set (0.02 sec)
多列去重
语法:
SELECTcolumnsFROM table_name WHERE where_conditions GROUPBYcolumns;
执行:
mysql> select sex,age from student groupby sex,age; +--------+------+ | sex | age | +--------+------+ | male | 10 | | female | 12 | | male | 11 | | male | NULL | | female | 11 | +--------+------+ 5 rows in set (0.03 sec)
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.
大致解释一下:
GROUP BY 默认隐式排序(指在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下也会进行排序)。然而,GROUP BY 进行显式或隐式排序已经过时(deprecated)了,要生成给定的排序顺序,请提供 ORDER BY 子句。
Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
大致解释一下:
从前(MySQL5.7 版本之前),Group by 会根据确定的条件进行隐式排序。在 MySQL 8.0 中,已经移除了这个功能,所以不再需要通过添加order by null来禁止隐式排序了,但是,查询结果可能与以前的 MySQL 版本不同。要生成给定顺序的结果,请按通过ORDER BY指定需要进行排序的字段。