之前在学习SQL时刷过一遍LeetCode上的SQL题，不过只做一遍效果并不是很好，很快也忘记了具体的解题思路。在这里将对其中的：Q176(第二高薪水) 、 Q177(第N高薪水) 、 Q178(分数排名) 、 Q184(部门工资最高的员工) 、 Q185(部门工资前三高的员工) 进行归纳总结，从而更进一步的去理解有关排名和分组筛选相关的问题。
LeetCode上的SQL答案可详见Github-LeetCode，欢迎Start，Issue
Leetcode上这五道题放在一起看，其考察的知识点可以拓展为下面三个方向：

分组筛选问题(最大值、第N个值、前N个值)
排名问题

CREATE TABLE `empl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` int(11) NOT NULL,
  `deparment` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这里插入图片描述

不分组筛选 - 获取前N个值
自身左连接法
筛选empl表中薪水最大的前三个值，实现思路：假设数据集中没有重复数据，则不难想到，最大的值不存在比其自身更大的数据项，第二大值只存在一个比自己大的数据项，第三大值有且只有两个比自己大的值；以此类推将自身和比自己大的数据表做join，则可以通过分组计数的方式获取前N个值。SQL如下

SELECT
	a.salary AS salary
FROM
	(
		SELECT DISTINCT
			salary
		FROM
			empl
	) a
LEFT JOIN (
	SELECT DISTINCT
		salary
	FROM
		empl
) b ON (a.salary < b.salary)
GROUP BY
	a.salary
HAVING
	count(*) < 3
ORDER BY
	salary DESC;

将上述left join改写成子查询，SQL如下：

SELECT
	salary
FROM
	(
		SELECT DISTINCT
			salary
		FROM
			empl
	) a
WHERE
	3 > (
		SELECT
			count(*)
		FROM
			(
				SELECT DISTINCT
					salary
				FROM
					empl
			) b
		WHERE
			a.salary < b.salary
	)
ORDER BY
	salary DESC;

分组筛选 左连接

实现筛选表employee中各部门薪水最高的前三个；只需要在上文的基础上，增加分组操作即可。同样需要保证数据集无不重复元素，

SELECT
	a.salary,
	a.deparment
FROM
	(
		SELECT DISTINCT
			salary,
			deparment
		FROM
			employee
	) a
LEFT JOIN (
	SELECT DISTINCT
		salary,
		deparment
	FROM
		employee
) b ON (
	a.deparment = b.deparment
	AND a.salary < b.salary
)
GROUP BY
	a.deparment,
	a.salary
HAVING
	count(*) < 3
ORDER BY
	a.deparment,
	a.salary DESC;

改写为子查询

SELECT
	*
FROM
	(
		SELECT DISTINCT
			deparment,
			salary
		FROM
			employee
	) a
WHERE
	3 > (
		SELECT
			count(*)
		FROM
			(
				SELECT DISTINCT
					deparment,
					salary
				FROM
					employee
			) b
		WHERE
			a.deparment = b.deparment
		AND a.salary < b.salary
	)
ORDER BY
	a.deparment,
	a.salary DESC;


CREATE TABLE `demo_user` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) CHARACTER NOT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('2', 'zhong', 1, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('3', 'zhong', 12, 'gsgfsfgs', '2021-11-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('1', 'zhong', 12, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('4', 'zhong', 15, 'gsgfsfgs', '2021-10-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('5', 'li', 12, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('6', 'li', 1, 'gsgfsfgs', '2021-11-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('7', 'li', 2, 'gsgfsfgs', '2021-10-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('8', 'li', 120, 'gsgfsfgs', '2021-10-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('9', 'wang', 12, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('10', 'wang', 3, 'gsgfsfgs', '2021-11-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('11', 'wang', 5, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('12', 'wang', 3, 'gsgfsfgs', '2021-11-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('13', 'huang', 1, 'gsgfsfgs', '2021-12-10 12:12:12');
INSERT INTO zhong_test_2.demo_user
(id, name, age, address, create_time)
VALUES('14', 'huang', 12, 'gsgfsfgs', '2021-10-10 12:12:12');

select * from zhong_test_2.demo_user origin 
	where 2 > ( select count(*) from zhong_test_2.demo_user du where du.name = origin .name and du.age > origin.age)
	order by origin.age desc;

解析：

     2 > : 表示获取每一类的数据的条数

    du.name = origin .name  ： 表示分组依据，按照name分组

    du.age > origin.age ： 排序方式，也就是获取前多少数据或者倒数多少条数据。> 表述获取前多少条数据，< 表示获取倒数多少条数据。

取前两名

select * from zhong_test_2.demo_user origin 
	where 2 > ( select count(*) from zhong_test_2.demo_user du where du.name = origin .name and du.age > origin.age)
	order by origin.age desc;

取后两名

	select * from zhong_test_2.demo_user origin 
	where 2 > ( select count(*) from zhong_test_2.demo_user du where du.name = origin .name and du.age < origin.age)
	order by origin.age desc;
	```
# mysql # 数据库 # dba
Logo
魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

cover

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

avatar 魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。

avatar 魔乐社区
cover

小杰机器学习（seven）——贝叶斯分类

avatar 魔乐社区