MySQL-常见业务/笔试题

简介

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

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

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

描述

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

id	name	salary	department_id
1	Joe	7000	1
2	Henry	8000	2
3	Sam	6000	2
4	Max	9000	1

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

id	name
1	IT
2	Sales

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

department	employee	salary
IT	Max	90000
Sales	Henry	80000

 建表语句：

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集合

略

按天为单位查询数量

略

给查询结果编号

略