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去重后的數(shù)目， SQL Server支持，而Access不支持
select count(distinct name, id) from A;   --SQL Server和Access都不支持

Example 4

select id, distinct name from A;   --會(huì)提示錯(cuò)誤，因?yàn)閐istinct必須放在開(kāi)頭

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.