SQL distinct usage

SQL SELECT DISTINCT statement
Duplicate values may exist in the table. This is not a problem. However, sometimes you may want to list only different values.

The keyword DISTINCT is used to return a unique value.

Syntax:
Select distinct column name FROM table name

To SELECT only different values from the Company column, use the select distinct statement:

The code is as follows:	Copy code
Select distinct Company FROM Orders

How to disable distinct keywords

The distinct keyword can only be used to filter all records in the query field that are the same (the record set is the same).

Distinct keywords are sorted, which is very inefficient.

Select distinct name from t1 can eliminate duplicate records, but only one field can be taken. Now, the value of id and name must be taken at the same time.
Select distinct id, name from t1 can take multiple fields, but only records with the same values of the two fields can be eliminated.
Therefore, you cannot use distinct to achieve the desired effect. You can use group by to solve this problem.

For example, the fields to be displayed are A, B, and C, and the content of field A cannot be repeated. You can use the following statement:

The code is as follows:	Copy code
Select A, min (B), min (C), count (*) from [table] where [condition] group by Having [condition] order by A desc

For better display of the title header, replace select A, min (B), min (C), count (*) with select A as A, min (B) as B, min (C)

C, count (*) as repeated times

The displayed fields and sorting fields must be included in group.
However, the displayed fields include min, max, count, avg, sum, and other aggregate functions.
Min (B), min (C), count (*)
The general condition is written after the where clause
Conditions with aggregate functions are written after having.
If having plus count (*)> 1 in the preceding statement, we can find records with repeat times of record A greater than 1.
If having plus count (*)> 2 in the preceding statement, we can find records with repeat times of record A greater than 2.
If having plus count (*)> = 1 in the preceding statement, all records can be queried, but only one record is displayed, and the number of repeat times is displayed later-

--- This is the expected result, and the statement can be passed through hibernate

The following statement can query the data that is duplicated:
Select Field 1, field 2, count (*) from table name group by field 1, field 2 having count (*)> 1

Change the ">" number above to "=" to query the non-duplicated data.

For example

The code is as follows:	Copy code
Select count (*) from (select gcmc, gkrq, count (*) from gczbxx_zhao t group by gcmc, gkrq having Count (*)> = 1 order by GKRQ) Recommended: Select * from gczbxx_zhao where viewid in (select max (viewid) from gczbxx_zhao group Gcmc) order by gkrq desc