How to use distinc in SQL

This article is about how to use distinc in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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

When using mysql, it is sometimes necessary to query out records that do not repeat in a certain field. Although mysql provides the keyword distinct to filter out redundant duplicate records and retain only one record, it is often used only to return the number of records that do not repeat, rather than to return all the values of records that are not duplicated. The reason is that distinct can only return its target field, but can not return other fields, this problem bothers me for a long time. If I can't solve it with distinct, I can only use double loop query to solve it. For a station with a very large amount of data, it will undoubtedly directly affect the efficiency, so I have wasted a lot of time.

Duplicate values may be included in the table. This is not a problem, but sometimes you may want to list only different (distinct) values. The keyword distinct is used to return a unique different value.

Table A:

Example 1

The code is as follows:

Select distinct name from A

The results are as follows:

Example 2

The code is as follows:

Select distinct name, id from A

The results are as follows:

In fact, it is based on "name+id". Distinct works on both name and id, which is supported by both Access and SQL Server.

Example 3: statistics

The code is as follows:

Select count (distinct name) from A;-- the number of deduplicated name in the table. SQL Server supports it, while Access does not support select count (distinct name, id) from A;-- neither SQL Server nor Access supports it.

Example 4

The code is as follows:

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

Other

The fields displayed by select in the distinct statement can only be fields specified by distinct, and other fields are not possible. For example, if Table A has a memo column, it is impossible to get distinc name and the corresponding memo field directly through distinct.

Thank you for reading! This is the end of this article on "how to use distinc in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!