The DISTINCT keyword can only filter all records in the query field (same Recordset), and if you want to specify a field without effect, another distinct keyword is sorted and inefficient.
The select distinct name from T1 can eliminate duplicate records, but only one field, and now the values of the Id,name 2 fields are taken.
SELECT DISTINCT id,name from T1 can take multiple fields, but only records with the same values of all 2 fields are eliminated
So using distinct can not achieve the desired effect, with group by to solve this problem.
For example, to display a field of a, B, C three, and the contents of a field can not be repeated with the following statement:
Select a, Min (B), Min (C), COUNT (*) from [table] where [condition] GROUP by A
Having [condition] ORDER BY A DESC
In order to display the title head good-looking point can be select a, min (b), Min (c), COUNT (*) to be replaced by select a as A, min (B) as B,min (C) as C,count (*) as repeat times
The fields and sort fields that are displayed are included in group by
However, the displayed fields are wrapped with aggregate functions such as min,max,count,avg,sum and can not be in GROUP by
Min (B) in the previous sentence, Min (C), COUNT (*)
General conditions are written in the where behind
Conditions that have aggregate functions are written after having
If you have added count (*) >1 in the previous sentence, you can detect records with a repeat number greater than 1
If you have added count (*) >2 in the previous sentence, you can detect records with a repeat number greater than 2
If you have add count (*) >=1 in the previous sentence, you can find all the records, but only one of them is duplicated, and there is a number of repetitions to show----This is the desired result, and the statement can pass through the hibernate
The following statement can query that the data is duplicated:
Select field 1, Field 2,count (*) from table name Group By field 1, Field 2 having count (*) > 1
Change the above > number to = number to query for no duplicate data.
For example
Copy Code code as follows:
Select COUNT (*) from (select Gcmc,gkrq,count (*) to Gczbxx_zhao T GROUP by GCMC,GKRQ
COUNT (*) >=1 ORDER by GKRQ)
SELECT * from Gczbxx_zhao where viewid, select Max (VIEWID) from Gczbxx_zhao GROUP by
GCMC) Order BY GKRQ desc---or is this feasible
。
One side of the question said: distinct to repeat the efficiency is very much, I see this article on the Internet method seems to say that the group by has a high efficiency?
I was in a test, there is a commodity table, 260,000 records, only the commodity number indexed, the Brand name field do distinct
Copy Code code as follows:
Select Brand,count (*) from Tab_commbaseinfo GROUP by Brand has count (*) =1
Average time is: 0.453
Copy Code code as follows:
SELECT DISTINCT brand from Tab_commbaseinfo
Average time is: 0.39
I don't know if there are any other ways.
