Leetcode 185. 部门工资前三高的所有员工 Apare_xzc

最新推荐文章于 2024-06-28 09:09:56 发布
最新推荐文章于 2024-06-28 09:09:56 发布
阅读量299 收藏
点赞数 1
分类专栏： 数据库 sql leetcode 文章标签： mysql leetcode python 编译原理 Cpp
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40531479/article/details/113879417
版权
数据库 同时被 3 个专栏收录
4 篇文章 0 订阅
订阅专栏
sql
4 篇文章 0 订阅
订阅专栏
leetcode
3 篇文章 0 订阅
订阅专栏

Leetcode 185. 部门工资前三高的所有员工

题目链接：185.部门公司前三高的所有员工

在这里插入图片描述

在这里插入图片描述

为了方便自己调试，我们可以在本地mysql建库建表

DROP DATEBASE IF EXISTS leetcode185;
CREATE DATABASE leetcode185;
USE leetcode185;

DROP TABLE IF EXISTS Employee;

CREATE TABLE `Employee` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(10) NOT NULL,
  `Salary` int NOT NULL,
  `DepartmentId` int NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS Department;

CREATE TABLE `Department` (
	`Id` int NOT NULL AUTO_INCREMENT,
	`Name` varchar(10) NOT NULL,
	PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Employee`(`Name`, `Salary`, `DepartmentId`) VALUES
	('Joe', 85000, 1),
	('Henry', 80000, 2),
	('Sam', 60000, 2),
	('Max', 90000, 1),
	('Janet', 69000, 1),
	('Randy', 85000, 1),
	('Will', 70000, 1);
INSERT INTO `Department`(`Id`, `Name`) VALUES (1, 'IT'), (2, 'Sales');

我们可以查看我们建好的表

mysql> USE leetcode185;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_leetcode185 |
+-----------------------+
| Department            |
| Employee              |
+-----------------------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM Employee;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| Id           | int         | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(10) | NO   |     | NULL    |                |
| Salary       | int         | NO   |     | NULL    |                |
| DepartmentId | int         | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> SHOW COLUMNS FROM Department;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int         | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Employee;
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
|  1 | Joe   |  85000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
|  4 | Max   |  90000 |            1 |
|  5 | Janet |  69000 |            1 |
|  6 | Randy |  85000 |            1 |
|  7 | Will  |  70000 |            1 |
+----+-------+--------+--------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM Department;
+----+-------+
| Id | Name  |
+----+-------+
|  1 | IT    |
|  2 | Sales |
+----+-------+
2 rows in set (0.01 sec)

mysql>

没什么问题的话我们就可以开始思考。

首先，sql的底层实现肯定也是遍历表。链接查询就是笛卡尔积，说白了就是两层for循环。
我们可以用C来写个伪代码：

有这样一种思路：同一部门，所有人的工资放在一起，排序去重，找出前三大，然后再扫描所有人，看工资是否>=部门的第三大工资。
但是这样并不好实现。可能部门的工资去重之后没有3个。而且按照部门分组也不是很容易。

我们可以这样考虑，对于每个一人是否选择，就是看在同一部门中，比他工资高的人的工资去重之后是否小于3。

# Write your MySQL query statement below
SELECT d.Name AS 'Department', e.Employee, e.Salary
FROM Department d INNER JOIN (
    SELECT NAME AS 'Employee', Salary, DepartmentId
    FROM Employee e1
    WHERE 3 > (
        SELECT COUNT(DISTINCT Salary)
        FROM Employee e2
        WHERE e1.DepartmentId = e2.DepartmentId
            AND e2.Salary > e1.Salary
        )
    ) e
ON d.Id = e.DepartmentId;

我们来看看leetcode的输入格式

{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 85000, 1], [2, "Henry", 80000, 2], [3, "Sam", 60000, 2], [4, "Max", 90000, 1], [5, "Janet", 69000, 1], [6, "Randy", 85000, 1], [7, "Will", 70000, 1]], "Department": [[1, "IT"], [2, "Sales"]]}}

显然是json格式。
我们可以将其进行格式化，便于观察

{
    "headers": {
        "Employee": ["Id", "Name", "Salary", "DepartmentId"],
        "Department": ["Id", "Name"]
    },
    "rows": {
        "Employee": [[1, "Joe", 85000, 1], [2, "Henry", 80000, 2], [3, "Sam", 60000, 2], [4, "Max", 90000, 1], [5, "Janet", 69000, 1], [6, "Randy", 85000, 1], [7, "Will", 70000, 1]],
        "Department": [[1, "IT"], [2, "Sales"]]
    }
};

完全格式化：

{
	"headers": {
		"Employee": ["Id", "Name", "Salary", "DepartmentId"],
		"Department": ["Id", "Name"]
	},
	"rows": {
		"Employee": [
			[1, "Joe", 85000, 1],
			[2, "Henry", 80000, 2],
			[3, "Sam", 60000, 2],
			[4, "Max", 90000, 1],
			[5, "Janet", 69000, 1],
			[6, "Randy", 85000, 1],
			[7, "Will", 70000, 1]
		],
		"Department": [
			[1, "IT"],
			[2, "Sales"]
		]
	}
}

我们可以发现，这个json字符串给我们提供了如下的信息：该数据库中所有表的所有列名，以及所有表的所有行的数据。
headers 记录了所有的表头信息
rows 记录了所有要插入的数据

我们可以写出相应的文法G
这里我们规定<>尖括号中间的标识符为非终结符，-> 表示“生成”，“推导出”，ϵ表示空串，reg开头的表示正则表达式//表示注释
<>

<db_test> -> {} | {<table_defination>, <table_data>}
<table_defination> -> ϵ ｜ "headers": {<table_column_names>}
<table_column_names> -> ϵ | <table_column_names>, <column_name_kv>
<column_name_kv> -> <table_name>: [<column_name_list>]
<table_name> -> <property_name>  
<property_name> -> <identifier_name>  // 变量标识符
<identifier_name> -> "<identifier>" 
<identifier> -> <alpha> ｜ _ | <identifier><alp_num>
<alpha> -> reg[a-zA-Z]  // 英文字母
<alp_num> -> reg[a-zA-Z0-9_] // 英文字母或数字或下划线
<column_name_list> -> [<column_names>]
<column_names> -> <column_name> | <column_names>, <column_name>
<column_name> -> <identifier_name>

<table_data> -> ϵ | "row": {<table_row_datas>}
<table_row_datas> -> ϵ | <table_row_datas>, <row_data_kv>
<row_data_kv> -> <table_name>: [<rows_list>]
<rows_list> -> <one_row> | <rows_list>, <one_row>
<one_row> ->[<column_values>]
<column_values> -> <value_type> | <column_values>, <value_type>
<value_type> -> <number> | <str> // json的值类型可以看作只有数字和字符串
<str> -> <identifier_name>
<number> -> [1-9] | <number>[0-9] //假设都是正整数

文法和C解析再说，显然解析json可以直接用python的json模块

#!/usr/bin/python3

import json;
#db_json = '{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 85000, 1], [2, "Henry", 80000, 2], [3, "Sam", 60000, 2], [4, "Max", 90000, 1], [5, "Janet", 69000, 1], [6, "Randy", 85000, 1], [7, "Will", 70000, 1]], "Department": [[1, "IT"], [2, "Sales"]]}}'

def get_insert_sql_from_json(db_json):
    db_obj = json.loads(db_json)
    headers = db_obj.get("headers")
    rows = db_obj.get("rows")
    for tb_name in rows.keys():
        print ('DELETE FROM ' + tb_name + ';')
        column_names = headers.get(tb_name)
        column_count = len(column_names)
        if column_count == 0:
            continue
        all_rows = rows.get(tb_name)
        line1 = 'INSERT INTO ' + tb_name + '(' + column_names[0]
        for i in range(1, column_count):
            line1 += ', ' + column_names[i]
        line1 += ') VALUES'
        print (line1)
        row_count = len(all_rows)
        for i in range(0, row_count):
            one_row = all_rows[i]
            if len(one_row) != column_count:
                continue
            line2 = '('
            line2 += '"'+one_row[0]+'"' if type(one_row[0]) == type('') else str(one_row[0]) 
            for j in range(1, column_count):
                line2 += ', '
                line2 += '"'+one_row[j]+'"' if type(one_row[j]) == type('') else str(one_row[j])
            line2 += ');' if i == row_count-1 else '),' 
            print ('   ',line2)
            

if __name__ == "__main__":
    db_json = input()
    get_insert_sql_from_json(db_json)

在这里插入图片描述
在这里插入图片描述
显然这是我们期望的输出

DELETE FROM Employee;
INSERT INTO Employee(Id, Name, Salary, DepartmentId) VALUES
    (1, "Joe", 85000, 1),
    (2, "Henry", 80000, 2),
    (3, "Sam", 60000, 2),
    (4, "Max", 90000, 1),
    (5, "Janet", 69000, 1),
    (6, "Randy", 85000, 1),
    (7, "Will", 70000, 1);
DELETE FROM Department;
INSERT INTO Department(Id, Name) VALUES
    (1, "IT"),
    (2, "Sales");

在这里插入图片描述
可以直接粘贴到navicat中运行。
显然也可以用with open 文件输入输出，或者直接用终端pipe重定向。
在这里插入图片描述

至此，这道题引发的思考告一段落。
待续…

Apare_xzc
关注
专栏目录
Leetcode困难之185.部门工资前三高的所有员工
Janice的博客
03-25 839
表: Employee Column Name Type id int name varchar salary int departmentId int Id是该表的主键列。 departmentId是Department表中ID的外键。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。 表: Department Column Name Type id int name varchar Id是该表的主键列。 该表的每一行表示部门I
【LeetCode-SQL】185. 部门工资前三高的所有员工
HeavenDan的博客
12-27 2178
目录一、题目二、解决1、2、3、4、三、参考 一、题目 二、解决 1、 思路： 代码： 2、 思路： 代码： 3、 思路： 代码： 4、 思路： 代码： 三、参考 1、 2、 3、
SQL第3天：部门工资前三高的所有员工
m0_58028961的博客
02-27 1326
SQL架构 表:Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ Id是该表的主键列。 departme.
Leetcode第185场周赛
彩虹糖的博客
04-21 294
1417. 重新格式化字符串 签到题，没什么好说的，比赛的时候写的复杂了一些。 class Solution { public String reformat(String s) { char [] arr = s.toCharArray(); ArrayList<Character> cs1 = new ArrayList<C...
LeetCode 185, 30, 230
最新发布
qq_61350148的博客
06-28 763
使用mysql和java语言解决LeetCode 185, 30, 230。
LeetCode: 185. Department Top Three Salaries
杨领well的专栏
08-24 222
LeetCode: 185. Department Top Three Salaries 题目描述 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+------...
LeetCode知识点总结 - 185
m0_59773145的博客
12-30 516
LeetCode 185. Department Top Three Salaries 考点 难度 Database Hard 题目 A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the
【LeetCode】打卡--SQL185. 部门工资前三高的员工
Asher117的博客
03-27 379
【LeetCode】打卡–SQL185. 部门工资前三高的员工 题目 Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+-...
Sql力扣算法：185. 部门工资前三高的所有员工
黄瓜炒鸡蛋的博客
11-02 1265
Sql力扣算法：185. 部门工资前三高的所有员工
力扣185题： 部门工资前三高的所有员工
sinat_41876868的博客
12-22 681
1、找出每个员工工资信息及部门信息，两个表连接即可： select * from Employee left join Department on employee.DepartmentId=Department.id 2、步骤1的结果不符合题目要求，找出每个部门工资前三高的： select * from Employee left join Department on employee.DepartmentId=Department.id where (DepartmentId,salary...
LeetCode-185. 部门工资前三高的所有员工( 185. Department Top Three Salaries)。
Hello_JavaScript的博客
08-10 1396
Employee 表包含所有员工信息，每个员工有其对应的工号Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Jo...
LeetCode185-MySQL-部门工资前三高的员工
希尔芬酱的博客
07-09 2959
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 ...
leetcode sql 185. 部门工资前三高的员工
qq_15181927的博客
09-14 499
185. 部门工资前三高的员工   Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1...
leetCode185富有和安静
zyzn1425077119的博客
12-26 131
package com.douyin; import java.util.ArrayList; import java.util.HashSet; import java.util.Set; /* https://blog.csdn.net/weixin_43175029/article/details/92842259 题意： 在一组N个人(0,1,2,,,,N-1),每个人都有不同的钱以...
LeetCode 185. Department Top Three Salaries
T Yuan
04-04 1275
select c.Name, a.Name, a.salary from Employee a, ( select ifnull(b.c,0) as c,a.Id from Employee a left join ( select count(1) as c,a.Id from Employee a,(select DepartmentId,salary
Leetcode185: Integer to English Words
LX
12-07 308
Convert a non-negative integer to its english words representation. Given input is guaranteed to be less than 231 - 1. For example, 123 -> "One Hundred Twenty Three" 12345 -> "Twelve Thousand Th
LeetCode185——部门工资前三高的员工
清风阁
01-22 288
我的LeetCode代码仓：https://github.com/617076674/LeetCode 原题链接：https://leetcode-cn.com/problems/department-top-three-salaries/description/ 题目描述： 知识点：JOIN连接查询、子查询、DISTINCT关键字 思路：JOIN子句实现两表连接查询，子查询查询薪水排...
leetcode185. 部门工资前三高的员工
菜鸟腾飞
01-04 508
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。 ±—±------±-------±-------------+ | Id | Name | Salary | DepartmentId | ±—±------±-------±-------------+ | 1 | Joe | 70000 | 1 ...
LeetCode_OJ【185】Department Top Three Salaries
hb_peng的专栏
09-13 873
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----
leetcode2020.9.18：解决最长回文子串问题
资源摘要信息:"leetcode答案-2020.9.18:2020.9.18" 知识点: 1. 回文子串定义: 回文串是正读和反读都相同的字符串，例如"aba"、"cbbd"。在编程问题中寻找最长的回文子串是一个常见的算法问题。 2. LeetCode平台: ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值