mysql中聚合函数max的sql分组

r7xajy2e 于 2021-06-20 发布在 Mysql

关注(0)|答案(5)|浏览(325)

我有如下表格：

+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+

查询

select dept_name, max(salary) from instructor group by dept_name;

会给我教员每个部门的最高工资。
但是，我想得到结果中的所有列，也就是每个部门中工资最高的讲师的id和姓名。但我不知道该怎么做。

sql mysql group-by Max

来源：https://stackoverflow.com/questions/52051181/grouping-by-aggregate-function-max-in-mysql

5条答案

按热度按时间

oprakyz71#

可以将当前查询用作筛选原始表的子查询：

SELECT i1.*
FROM instructor i1
INNER JOIN
(
    SELECT dept_name, MAX(salary) AS salary
    FROM instructor
    GROUP BY dept_name
) i2
    ON i1.dept_name = i2.dept_name AND i1.salary = i2.salary;

赞(0）回复(0）举报 2021-06-21

az31mfrm2#

SELECT ins.id , ins.name 
FROM instructor ins 
inner join (
    SELECT ins2.id as id , MAX(ins2.salary) as salary 
    FROM instructor ins2 
    group by ins2.id
) as insG 
ON ins.id = insG.id AND ins.salary = insG.salary

赞(0）回复(0）举报 2021-06-21

wfveoks03#

可以使用相关子查询：

select i.*
from instructor i
where salary = (select max(i1.salary)
                from instructor i1
                where i1.dept_name = i.dept_name
               );

赞(0）回复(0）举报 2021-06-21

zzoitvuj4#

也可以使用相关子查询

select t.* from instructor  t
 where salary in
  ( 
    select  max(salary) from instructor t1 
    where t1.dept_name=t.dept_name
    group by t1.dept_name 
   )

http://sqlfiddle.com/#!9/f7018c/1号楼

赞(0）回复(0）举报 2021-06-21

oxcyiej75#

请使用以下查询：

SELECT ID, name, dept_name, salary
FROM( select ID, name, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY  dept_name ORDER BY salary DESC) as ranks
 from instructor)T
 WHERE ranks = 1

赞(0）回复(0）举报 2021-06-20

我来回答

mysql中聚合函数max的sql分组

5条答案

相关问题

热门标签

最新问答