Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use distinct in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

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 copy code is as follows: select distinct name from A

The results are as follows:

Example 2

The copy 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 copy code is as follows: select count (distinct name) from A;-- the number of deduplicated name in the table, which is supported by SQL Server, while Access does not support select count (distinct name, id) from A;-- SQL Server and Access do not support

Example 4

The copy code is as follows: select id, distinct name from A;-- prompts 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.

After reading the above, do you have any further understanding of how to use distinct in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.