SQL distinct statement

The distinct keyword of the SQL distinct statement is used to filter out redundant duplicate records and retain only one record, but it is often used to return the number of records that are not repeated, instead of using it to return all non-record values. The reason is that distinct is only used for dual-loop queries, which will undoubtedly directly affect the efficiency of a station with a large data volume. -Let's take a look at the example below: The table Field 1 Field 2 id name 1 a 2 B 3 c 4 c 5 B library structure is like this, this is just a simple example, 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 c seems to have achieved results, but what do I want to get is the id value? Modify the query statement:

select distinct name, id from table 

 

The result will be: ---------- id name 1 a 2 B 3 c 4 c 5 B distinct. Why didn't it work? The function is effective, but it also applies to two fields, that is, it must have the same id and name to be excluded and then modify the query statement:

select id, distinct name from table 

 

Unfortunately, you cannot get anything except the error message. You must start with distinct. Is it difficult to place distinct in the where condition? Yes. An error is returned. -------------------------------------------------------- The following method is feasible:

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. To achieve group by, you must put it before order by and limit. Otherwise, an error will be reported.