MySQL Distinct&group by app

When using MySQL, it is sometimes necessary to query for a record that does not duplicate a field, although MySQL provides the DISTINCT keyword to filter out redundant duplicate records to keep only one, but it is often used only to return the number of distinct records, instead of using it to return all values that are not re-recorded. The reason is that distinct can only return its target field, and can not return other fields, this problem has plagued me for a long time, with distinct can not solve the words, I only use a double-loop query to solve, and so for a very large number of stations, will undoubtedly directly affect the efficiency. So I spent a lot of time studying the problem, and I couldn't find a solution on the Internet.

Let's take a look at the example below:

Table
ID Name
1 A
2 b
3 C
4 C
5 b

The library structure is probably like this, this is just a simple example, the actual situation will be much more complex.

For example, if I want to use a single statement to find all the data that name does not repeat, then you must use distinct to remove the redundant duplicate records.

Select DISTINCT name from table
The resulting results are:

Name
A
B
C

It seems to be working, but what I want to get is the ID value? Change the query statement:

Select DISTINCT name, ID from table

The result would be:

ID Name
1 A
1 A
3 C
4 C
5 b

Why doesn't distinct work? The role is up, but he also functions two fields, that is, the ID must be the same as the name will be excluded ....

Let's change the query statement:

Select ID, distinct name from table

Unfortunately, in addition to the error message you get nothing, distinct must be placed at the beginning. Is it difficult to put distinct in a where condition? Can, still error ....

It's a lot of trouble, huh? Indeed, no effort has been able to solve the problem.

With the Count function a try, success, I ... Want to cry Ah, took so much work ... The original is so simple ...

Now release the full statement:

SELECT *, COUNT (distinct name) from the table group by name

Results:

ID Name count (distinct name)
1 a 1
2 B 1
3 C 1

The last item is superfluous, do not care on the line, the purpose to achieve ...

Alas, the original MySQL so stupid, gently put him to cheat over, depressed also on me (yes, and let the guy), now take out hope you don't be this problem toss.

Oh, yes, and by the way, group by MySQL Group by usage parsing (verbose)

Wednesday, 09/21/2011-14:33- Jason

Group BY usage Analysis
The group by syntax can group the results of a query based on each member of a given data column, resulting in a grouped summary table.
The column name in the SELECT clause must be a grouping column or a column function. The column function returns a result for each group defined by the GROUP BY clause.
The structure and data of an employee information table are as follows:
ID Name Dept Salary edlevel HireDate
1 Zhang Three Development Department 2000 3 2009-10-11
2 Li Si Development Department 2500 3 2009-10-01
3 Harry Design Department 2600 5 2010-10-02
4 Wangliuqi Design Department 2300 4 2010-10-03
5 MA Seven Design Department 2100 4 2010-10-06
6 Zhao Eight Sales Department 3000 5 2010-10-05
7 Money Nine Sales Department 3100 7 2010-10-07
8 Sun 10 Sales Department 3500 7 2010-10-06
For example, I want to list the results of the highest salary for each department, and the SQL statements are as follows:
SELECT DEPT, MAX (SALARY) as MAXIMUM
From staff
GROUP by DEPT
The query results are as follows:
DEPT MAXIMUM
Development Department 2500
Design Department 2600
Sales Department 3500
Explain the result:
1. The column name in the SELECT clause must be a grouping column or column function, because select has the column dept contained in the GROUP by Dept.
2. The column function returns one result per group defined by the GROUP BY clause, and returns a result for each department according to the Department group, which is the highest salary for each department.
Note: The calculation is for each department (the group defined by the groups by clause) instead of the entire company's MAX (SALARY).
For example, query the total number of salaries for each department
SELECT DEPT, SUM (SALARY) as Total
From staff
GROUP by DEPT
The query results are as follows:
DEPT Total
Development Department 4500
Design Department 7000
Sales Department 9600
use a WHERE clause with a GROUP by clause
A group query can have a standard WHERE clause that eliminates unqualified rows before forming groups and calculating column functions. You must specify a WHERE clause before the GROUP BY clause.
For example, find the highest salary for each level of the company's 2010 entry
SELECT DEPT, EDLEVEL, MAX (SALARY) as MAXIMUM
From staff
WHERE hiredate > ' 2010-01-01 '
GROUP by DEPT, EDLEVEL
ORDER by DEPT, EDLEVEL
The query results are as follows:
DEPT EDLEVEL MAXIMUM
Design Department 4 2300
Design Department 5 2600
Sales Department 5 3000
Sales Department 7 3500
Note: Each column name specified in the SELECT statement is also mentioned in the GROUP BY clause. The column names not mentioned in these two places produce an error.
The GROUP by clause returns one row for each unique combination of dept and Edlevel.
use the HAVING clause after the GROUP BY clause
You can group by applying a qualification so that the system returns results only to groups that meet the criteria. To do this, include a HAVING clause after the GROUP BY clause. A HAVING clause can contain one or more predicates that are joined with and and OR. Each predicate compares a group attribute, such as AVG (SALARY), with one of the following:
For example: Looking for the highest and lowest salary of a department with more than 2 employees:
SELECT DEPT, MAX (SALARY) as MAXIMUM, MIN (SALARY) as MINIMUM
From staff
GROUP by DEPT
Having COUNT (*) >2
ORDER by DEPT
The query results are as follows:
DEPT MAXIMUM MINIMUM
Design Department 2600 2100
Sales Department 3500 3000
For example: Find the highest and lowest salary for a department with an employee average salary greater than 3000:
SELECT DEPT, MAX (SALARY) as MAXIMUM, MIN (SALARY) as MINIMUM
From staff
GROUP by DEPT
Having AVG (SALARY) >3000
ORDER by DEPT
The query results are as follows:
DEPT MAXIMUM MINIMUM
Sales Department 3500 3000

MySQL Distinct&group by app