This is two table
The CREATE table dept (dept1 VARCHAR (6), dept_name VARCHAR (20)) default charset=utf8; INSERT into dept VALUES (' 101 ', 'financial'); INSERT into dept VALUES (' 102 ', 'sales'); INSERT into dept VALUES (' 103 ', 'technical'); INSERT into dept VALUES (' 104 ', 'executive'); The CREATE table emp (sid VARCHAR (6), the name VARCHAR (20), age TINYINT (2), woektime_start VARCHAR (10), incoming SMALLINT (10), dept2 VARCHAR (6) the default charset=utf8; Insert into emp VALUES (' 1789 ', 'zhang, 35,' 1980/1/1 '4000,' 101 '); Insert into emp VALUES (' 1674 ', 'li si, 32,' 1983/4/1 '3500,' 101 '); Insert into emp VALUES (' 1776 ', 'Cathy', 24, '1990/7/1' 2000, '101'); Insert into emp VALUES (' 1568 ', 'zhao six, 57,' 1970/10/11 '7500,' 102 '); Insert into emp VALUES (' 1564 ', 'honor seven, 64,' 1963/10/11 '8500,' 102 '); Insert into emp VALUES (' 1879 ', 'cattle eight, 55,' 1971/10/20 '7300,' 103 ');


This is problem

Best-paid employee name list of each department, the department name, income, and in accordance with the income descending


Which is


Select the name, dept_name, incoming from (select * from dept left join emp on dept. Dept1=emp. Dept2 order by incoming desc) s group by dept_name order by incoming desc;


My doubt is, why don't Max function, through the subquery can found out the maximum?? (I just started, so the question is simple for bosses, forgive me ha ) hope leaders can help me to reassure

CodePudding user response:

Whether in front of the query using the set rowcount 1, similar to the effect?
The above statement because of you, seems to run an error oh!

CodePudding user response:

- select dept2, MAX (incoming) highest wages from emp group by dept2 - this is to find the highest salary each department and the department number,


The select e. * from emp e,
(select dept2, MAX (incoming) highest wages from emp group by dept2) as M
Where e.d ept2=m. ept2 and e.i ncoming=m. with the highest wages, salaries and department staff table corresponding to, is the one we're looking for a
The order by e.i ncoming desc

CodePudding user response:

reference 1st floor qq_25073223 response:

is used in front of the query set rowcount 1 ah, like this effect?
The above statement because of you, seems to run an error oh!

No, it is concluded that the correct answer directly

CodePudding user response:

I perform your statements, do not add any aggregation function, get should be MIN aggregation function effect, grouping is not MAX, MYSQL query some default mechanism of
Or Suggestions according to the columns at the specified except group aggregation, or sometimes found out the results don't match