SQL mistakes-single-value rules and solutions in SQL grouping Query

Grouping query of SQL is very useful, but sometimes its features are also annoying. Take a good look at this SQL:

select employer_name, department, max(salary) from employer_salary group by department;

Query the name and salary number of the highest-paying person in each department. Is there a problem with this SQL statement.

This rule violates the single-value rule in the SQL grouping query. The common point is that in the grouping query, the column followed by the Select keyword must be the same value in each group, for example, because the Department Group is divided by department, it must be the same, and Max (salary) is also a single value. This is the so-called "Single-value rule ".

In general, we need columns that cannot meet the single-value rules when performing grouping queries, such as employer_name. Before we know this rule, we always guess that grouping queries should be smart enough to determine the name of the employee who needs the highest salary (max (salary, in fact, this is wishful thinking.

1. If there are more than one max (salary) person, which of the following should employer_name display?

2. Which of the following should I put if Max is not AVG, sum, Count employer_name?

So do not make it difficult for SQL. You have to think about it yourself.

The general processing method is basically as follows:

select a.* from employer_salary a JOIN (select employer_name, department, max(salary) as salary from employer_salary group by department) b on a.department = b,department and a.salery = b.salary;

Is it troublesome or not troublesome, that is, the efficiency is not high. In addition, the book SQL anti-pattern also provides several solutions, which are not ideal. One solution is clever, but it is obscure and hard to understand. It is written for everyone to worship the film:

select a.* from (employer_salery a LEFT JOIN employer_salary b ON a.department = b.department and a.salary < b.salary)where b.department is NULL;

Do you have any better solutions? Unfortunately, there is no standard SQL. Fortunately, Major dB brands have their own solutions, I will talk about its solution when I use PostgreSQL recently.

PostgreSQL window function:

Or the above case will use the window function as follows:

select a.employer_name, a.department, a.salary from (select employer_name, department, salary, max(salary) as max_salary OVER(DEPARTMENT BY department)) as awhere a.salary = a.max_salary

It seems complicated, but it is easy to understand.

Please refer to the second row. The difference between the window function and the standard SQL grouping function is that it does not comply with the single-value rule, the query results are not limited to one row, but all the result sets before the query are retained and the Results After grouping query are appended. This makes it easy to compare the results of the original column and Aggregate functions.

In addition, SQL statements that do not comply with the single-value rule will generally receive an error reported by the DB except MySQL and SQLite. They will return uncertain values without reporting an error, which is highly concealed and requires attention.