Use distinct to remove duplicate items in SQL

The distinct keyword can be used to remove duplicate rows from the results of the SELECT statement. If no
Distinct: All rows, including duplicate rows, are returned. For example, if you select all author IDs in titleauthor
Distinct, the following rows will be returned (including some repeated rows ):

Use pubs
Select au_id
From titleauthor

The following is the result set:

Au_id
-----------
172 to 32-1176
213 to 46-8915
213 to 46-8915
238-95-7766.
267 to 41-2394
267 to 41-2394
September 274-80-9391
409 to 56-7008
427-17-2319
472-27-2349
486 to 29-1786
486 to 29-1786
648 to 92-1872
672 to 71-3249
712 to 45-1867
722-51-5454
September 724-80-9391
September 724-80-9391
756-30-7391
807-91-6654.
846 to 92-7186
899 to 46-2035
899 to 46-2035
998 to 72-3567
998 to 72-3567

(25 row (s) affected)

After distinct is used, duplicate items can be removed, and only the unique Author ID can be viewed:

Use pubs
Select distinct au_id
From titleauthor

The following is the result set:

Au_id
-----------
172 to 32-1176
213 to 46-8915
238-95-7766.
267 to 41-2394
September 274-80-9391
409 to 56-7008
427-17-2319
472-27-2349
486 to 29-1786
648 to 92-1872
672 to 71-3249
712 to 45-1867
722-51-5454
September 724-80-9391
756-30-7391
807-91-6654.
846 to 92-7186
899 to 46-2035
998 to 72-3567

(19 row (s) affected)

The output of statements that involve distinct depends on the column sorting rule or the expression that uses distinct. For more information about the effects of different sorting rules, see basic knowledge of SQL Server sorting rules.

For the distinct keyword, each null value is considered to be a duplicate content. When the SELECT statement contains distinct, only one null value is returned in the result no matter how many null values are encountered.

Description to be compatible with SQL-92 standards and other Microsoft SQL Server versions, the All keyword can explicitly request all rows. However, since all is the default value, you do not need to specify it.

 

What is the usage of the distinct keyword in select?

In
When using MySQL, you sometimes need to query records that do not repeat a field. Although MySQL provides the keyword distinct to filter out redundant duplicate records and keep only one record
It returns the number of records that do not repeat, instead of using it to return all values that do not record duplication. The reason is that distinct can only return its target field, but cannot return other fields. This problem troubles me.
For a long time, if distinct cannot be used to solve the problem, I only need to use dual-loop queries. This will undoubtedly directly affect the efficiency of a station with a large amount of data. So I spent a lot of time
When I studied this problem, I couldn't find a solution on the Internet. During this period, I pulled Rong to help. The result was that we both were depressed .........

Let's take a look at the example below:

Table
ID name
1
2 B
3 C
4 C
5 B

The library structure is like this. This is just a simple example, and the actual situation is much more complicated.

For example, if you want to use a statement to query all data with no duplicate names, you must use distinct to remove redundant duplicate records.

Select distinct name from table
The result is:

Name
A
B
C

It seems that the effect has been achieved, but what I want to get is the ID value? Modify the query statement:

Select distinct name, ID from table

The result is:

ID name
1
2 B
3 C
4 C
5 B

How does distinct not work? It works, but it also applies to two fields, that is, it must have the same ID and name to be excluded .......

Modify the query statement again:

Select ID, distinct name from table

Unfortunately, you cannot get anything except the error message. You must start with distinct. Can't I put distinct in the where condition? Yes. An error is reported as a result .......

Very troublesome? Indeed, this problem cannot be solved with all the effort. No way. Continue to ask.

He grabbed a Java programmer in the company and showed me the solution in MySQL after using distinct in Oracle. Before leaving work, he suggested that I try group.

I tried it for a long time, and I couldn't do it. I finally found a usage in the MySQL manual. I realized what I needed with group_concat (distinct name) and group by name, try it now.

Error ............ Depressed ....... I can't even go through the MySQL manual. I gave me hope first, and then pushed me to disappointment ....

Check again. The group_concat function is supported by 4.1, dizzy. I have 4.0. No way. Upgrade. The upgrade is successful ......

Finally, the customer must be asked to upgrade.

Suddenly, the ghost machine flashed. Since the group_concat function can be used, can other functions be used?

Use the count function to try it out. I am a success ....... It takes so much time to cry ........ It turns out to be so simple ......

Now release the complete statement:

Select *, count (distinct name) from Table group by name

Result:

ID name count (distinct name)
1 A 1
2 B 1
3 c 1

The last item is redundant, so you don't have to worry about it. The goal is achieved .....

Alas, it turned out that mysql was so stupid that I would just lie to him with just a few clicks. I am so depressed (by the way, there is also the guy Rong). Now I hope you will not be overwhelmed by this problem.

Oh, yes. by the way, group by must be placed before order by and limit. Otherwise, an error will be reported.

 

Http://hi.baidu.com/anny406/blog/item/53b6b61e39c4b71e41341732%2Ehtml [Source]