2.4_4 记一次Mysql面试题 （Mysql5.7分组取前三-可重复）

相关链接

• Excel目录

---

一、题目介绍

  今天花了一个多小时做了某公司（数据分析师13-20k）一道sql笔试题，由于我仅有一个测试库为Mysql5.7的，而Mysql5.7不支持开窗函数，采用拼接方式 + 使用变量解决了这个问题，sql并没有很难的写法，主要是思路，下面先介绍题目

---

二、建表sql

  闲话不多扯我把建表语句先放在下面

-- ----------------------------
-- 员工表
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `salary` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `departmentid` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'Joe', '85000', 1);
INSERT INTO `employee` VALUES (2, 'Henry', '80000', 2);
INSERT INTO `employee` VALUES (3, 'Sam', '60000', 2);
INSERT INTO `employee` VALUES (4, 'Max', '90000', 1);
INSERT INTO `employee` VALUES (5, 'Janet', '69000', 1);
INSERT INTO `employee` VALUES (6, 'Randy', '85000', 1);
INSERT INTO `employee` VALUES (7, 'Will', '70000', 1);

-- ----------------------------
-- 部门表
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;


-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, 'IT');
INSERT INTO `department` VALUES (2, 'Sales');

---

三、思路介绍

  如果是oracle数据库可以用dense_rank()函数就可以很轻松的解决问题，相关用法可以参考这篇博客 (随便找的），由于我是随便找了个库测试，正好是Mysql5.7，就想办法在现有情况如何实现
  首先看题目要求，这里要求显示前三高工资员工，但不一定是三个人，可能大于三个人，所以不能按照这种思路：部门分组，按工资倒序排序，取前三名员工姓名做主键

  但是可以发现如果Department 和 Salary作为联合主键的话是可以按照取前三的方式执行的。所以我们现在要想办法拼接出来这个联合主键后，取这个联合主键的前三名，再将其作为关联条件查询employee和department就可以实现了，下面我将SQL拆成三部分进行讲解

---

3.1 按联合主键分组，薪资倒序

					select
						concat(t2.name,'-',t1.salary)as concat, -- 拼接部门+薪资作为主键
						t2.name as Department,
						t1.salary as Salary
					from
						employee t1
						left join department t2 on t1.departmentid = t2.id
					Group by 
						CONCAT(t2.name,t1.salary)
					order by t2.name,t1.salary desc

---

3.2 每个部门取前三

				select 
					if(t3.Department=@dep,@rownum := @rownum+1,@rownum:= 1) dep_sort,
					@dep:=t3.Department as Department, 
					t3.concat
				from 
				(
					select
						concat(t2.name,'-',t1.salary)as concat, -- 拼接部门+薪资作为主键
						t2.name as Department,
						t1.salary as Salary
					from
						employee t1
						left join department t2 on t1.departmentid = t2.id
					Group by 
						CONCAT(t2.name,t1.salary)
					order by t2.name,t1.salary desc
				)t3 
				join (SELECT @rownum:=0,@dep:=null) t4

---

3.3 按联合主键分组取前三

			select t5.concat from (
				select 
					if(t3.Department=@dep,@rownum := @rownum+1,@rownum:= 1) dep_sort,
					@dep:=t3.Department as Department, 
					t3.concat
				from 
				(
					select
						concat(t2.name,'-',t1.salary)as concat, -- 拼接部门+薪资作为主键
						t2.name as Department,
						t1.salary as Salary
					from
						employee t1
						left join department t2 on t1.departmentid = t2.id
					Group by 
						CONCAT(t2.name,t1.salary)
					order by t2.name,t1.salary desc
				)t3 
				join (SELECT @rownum:=0,@dep:=null) t4
			) t5 
			where t5.dep_sort<4 -- 每个部门取前三名

---

3.4 主SQL

select
	t2.name as Department,
	t1.name as Employee,
	t1.salary as Salary
from
	employee t1
	left join department t2 on t1.departmentid = t2.id
where 
	concat(t2.name,'-',t1.salary) -- 部门+薪资联合主键作为筛选条件
		in(
			-- 到这里就很简单了，没什么好讲的，放入3.3查询的sql即可
			)
order by 
	t2.name,t1.salary desc

---

四、解题sql

select
	t2.name as Department,
	t1.name as Employee,
	t1.salary as Salary
from
	employee t1
	left join department t2 on t1.departmentid = t2.id
where 
	concat(t2.name,'-',t1.salary) -- 部门+薪资联合主键作为筛选条件
		in(
			select t5.concat from (
				select 
					if(t3.Department=@dep,@rownum := @rownum+1,@rownum:= 1) dep_sort,
					@dep:=t3.Department as Department, 
					t3.concat
				from 
				(
					select
						concat(t2.name,'-',t1.salary)as concat, -- 拼接部门+薪资作为主键
						t2.name as Department,
						t1.salary as Salary
					from
						employee t1
						left join department t2 on t1.departmentid = t2.id
					Group by 
						CONCAT(t2.name,t1.salary)
					order by t2.name,t1.salary desc
				)t3 
				join (SELECT @rownum:=0,@dep:=null) t4
			) t5 
			where t5.dep_sort<4
		)
order by 
	t2.name,t1.salary desc

五、执行结果

---

21/05/13

M