Usage of distinct in SQL (analysis of four examples)

When using mysql, sometimes you need to query for unique records in a certain field. Although mysql provides the distinct keyword to filter out redundant duplicate records and keep only one, it is often only used to return the number of unique records. , instead of using it to return all values ​​without duplicate records. The reason is that distinct can only return its target field and cannot return other fields. This problem has troubled me for a long time. If it cannot be solved with distinct, I can only solve it with a double loop query, which is very difficult for a large amount of data. For the website, it will undoubtedly directly affect the efficiency, so I wasted a lot of time.

In the table, there may be duplicate values. This is not a problem, but sometimes you may want to just list distinct values. The keyword distinct is used to return uniquely distinct values.

Table A:

Example 1

select distinct name from A

The results after execution are as follows:

Example 2

select distinct name, id from A

The results after execution are as follows:

In fact, it is based on "name+id" To remove duplicates, distinct acts on both name and id. This method is supported by Access and SQL Server at the same time.

Example 3: Statistics

select count(distinct name) from A;   --表中name去重后的数目， SQL Server支持，而Access不支持
select count(distinct name, id) from A;   --SQL Server和Access都不支持

Example 4

select id, distinct name from A;   --会提示错误，因为distinct必须放在开头

Others

The fields displayed in the select statement can only be the fields specified by distinct, and other fields are not possible. For example, if table A has a "Remarks" column, if you want to obtain the distinct name and the corresponding "Remarks" field, it is impossible to do so directly through distinct.