Usage of distinct in SQL

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:

Table B:

1. Act on a single column

select distinct name from A

The result after execution is as follows:

2. Act on multiple columns

Example 2.1

select distinct name, id from A

The result after execution is as follows:

In fact, duplication is removed based on the two fields of name and id. This method is supported by Access and SQL Server at the same time.

Example 2.2

select distinct xing, ming from B

returns the following results:

The returned result is two rows, which shows that distinct is not a "string splicing" of the xing and ming columns. The deduplication is applied to the xing and ming columns respectively.

3.COUNT statistics

select count(distinct name) from A; --The number of names in the table after deduplication, SQL Server supports it, but Access does not support it

count cannot count multiple fields, as follows SQL fails to run in both SQL Server and Access.

select count(distinct name, id) from A;

If you want to use it, please use nested query, as follows:

select count(*) from (select distinct xing, name from B) AS M;

4.distinct must be placed at the beginning

select id, distinct name from A; --an error will be prompted because distinct must be placed at the beginning

5. Other

fields displayed in the select statement can only be specified by distinct fields, 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.