Detailed explanation of how to use distinct in SQL

The distinct keyword is used to filter out redundant duplicate records and keep only one, but it is often only used to return the number of unique records, rather than using it to return all values ​​​​of unique records. The reason is that distinct can only return its target field, but cannot return other fields. Next, I will share with you the usage of distinct in SQL through this article. Friends who need it can refer to it

When using mysql, sometimes it is necessary to query to get non-duplicate records in a certain field. Although mysql provides the distinct keyword to filter out redundant duplicate records and keep only one, but It is often only used to return the number of unique records, rather than using it to return all values ​​of non-duplicate records. The reason is that distinct can only return its target field, but 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, and this is For a site with a very large amount of data, it will undoubtedly directly affect the efficiency, so I wasted a lot of time.

The table may contain 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

The code is as follows:

select distinct name from A

The result after execution is as follows:

Example 2

The code is as follows:

select distinct name, id from A

The result after execution is as follows:

is actually based on "name+id", and distinct acts on name and id at the same time. This method Access and SQL Server are supported at the same time.

Example 3: Statistics

The code is as follows:

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

Example 4

The code is as follows:

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

Others

The fields displayed in the select statement in the distinct 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 get the distinct name and the corresponding "Remarks" field, it is impossible to do it directly through distinct.

Summary:

This article uses examples to introduce in detail the use of distinct in SQL. It is more intuitive. Everyone demonstrates the use of distinct. I believe everyone has their own understanding and knowledge. I hope it will be helpful to your work!

Related recommendations:
mysql count distinct statistical results to remove duplicates

Thoughts triggered by a distinct problem

Oracle—Usage of distinct

Usage of distinct in SQL (analysis of four examples)

The above is the detailed content of Detailed explanation of how to use distinct in SQL. For more information, please follow other related articles on the PHP Chinese website!