所有分类
  • 所有分类
  • 未分类

MySQL–常见业务/笔试题

简介

本文介绍MySQL的一些常见业务的语句。其中第一个(查询部门最高工资的员工信息)经常出现在Java后端的笔试题中。

其他几个业务不是特别常见,暂且放在这里,后边有时间再补充。

查询部门最高工资的员工信息

描述

表t_employee保存了所有的员工数据。

idnamesalarydepartment_id
1Joe70001
2Henry80002
3Sam60002
4Max90001

表t_department保存了所有的部门数据。

idname
1IT
2Sales

写一段SQL查找出各部门工资最高的员工信息。如上所示,IT部门工资最高的是Max,Sales部门工资最高的是Henry。

departmentemployeesalary
ITMax90000
SalesHenry80000

 建表语句:

CREATE TABLE `t_employee`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `department_id` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `t_employee` VALUES (1, 'Joe', 7000.00, 1);
INSERT INTO `t_employee` VALUES (2, 'Henry', 8000.00, 2);
INSERT INTO `t_employee` VALUES (3, 'Sam', 6000.00, 2);
INSERT INTO `t_employee` VALUES (4, 'Tony', 9000.00, 1);
CREATE TABLE `t_department`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `t_department` VALUES (1, 'IT');
INSERT INTO `t_department` VALUES (2, 'Sales');

方法1

SELECT
	t_employee.`name` AS employee,
	t_employee.`salary` AS salary,
	t_department.`name` AS department 
FROM
	t_employee,
	t_department,
	( SELECT max( `salary` ) AS salary, `department_id` FROM t_employee GROUP BY department_id ) AS max_salary 
WHERE
	t_employee.department_id = t_department.id 
	AND t_employee.salary = max_salary.salary

结果: 

方法2

SELECT
	t_employee.`name` AS employee,
	t_employee.`salary` AS salary,
	t_department.`name` AS department 
FROM
	t_employee,
	t_department 
WHERE
	t_employee.department_id = t_department.id 
	AND t_employee.salary >= (
		SELECT
			max( `salary` ) 
		FROM
			t_employee AS E2 
		WHERE
			t_employee.`department_id` = E2.`department_id`
	)

执行结果

拼接递归的父级id

获得表里最底层的id集合

按天为单位查询数量

给查询结果编号

2

评论3

请先

  1. SELECT t_employee.`name` AS employee, t_employee.`salary` AS salary, t_department.`name` AS department FROM t_employee, t_department WHERE t_employee.department_id = t_department.id AND t_employee.salary in ( SELECT MAX(t_employee.salary) FROM t_employee GROUP BY t_employee.department_id );
    run1 2024-04-02 0
  2. select d.`name`,e.name,max(e.salary) from employee e left join department d on e.department_id=d.id group by e.department_id
    秋风扫落叶 2023-07-21 0
    • SELECT、FROM、LEFT JOIN等关键字建议大写~
      自学精灵 2023-07-21 3
显示验证码
没有账号?注册  忘记密码?

社交账号快速登录