查询部门最高工资

CREATE TABLE `dep` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

CREATE TABLE `emp` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`salary` int(20) NOT NULL DEFAULT '0',
`did` int(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

 

select emp.name , emp.salary ,dep.name as Department FROM dep,emp where dep.id=emp.did and salary in ( select max( emp.salary ) from emp group by emp.did);