小黑身份证挂失登报，见到了几年不见的黑天鹅，肚子吃坏了疼了几天，投资自己尝试健身，去平谷吃羊肉准备去保定找小老黑的leetcode之旅:220. 存在重复元素 III

本文主要是介绍小黑身份证挂失登报，见到了几年不见的黑天鹅，肚子吃坏了疼了几天，投资自己尝试健身，去平谷吃羊肉准备去保定找小老黑的leetcode之旅:220. 存在重复元素 III，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

滑动窗口 + 有序集合

class Solution:def containsNearbyAlmostDuplicate(self, nums: List[int], indexDiff: int, valueDiff: int) -> bool:# 数组长度n = len(nums)# 初始化有序集合from sortedcontainers import SortedSetsort_set = SortedSet()window_size = indexDiff + 1for i in range(n):if i >= window_size:sort_set.remove(nums[i-window_size])if i:p = self.left_search(sort_set, nums[i]-valueDiff)if p < len(sort_set) and sort_set[p] <= nums[i]+valueDiff:return Truesort_set.add(nums[i])return Falsedef left_search(self, arr, target):# 初始化头尾指针n = len(arr)head = 0tail = n - 1while head <= tail:mid = (tail + head) // 2if arr[mid] < target:head = mid + 1else:tail = mid - 1return head

在这里插入图片描述

装桶法

class Solution:def containsNearbyAlmostDuplicate(self, nums: List[int], indexDiff: int, valueDiff: int) -> bool:# 得到桶的编号def get_id(target, size):return target // size# 数组长度n = len(nums)# 存放窗口内桶中的元素信息map_ = {}# 桶的大小size = valueDiff + 1# 窗口大小window_size = indexDiff + 1# 开始进行装桶for i in range(n):# 滑动窗口if i >= window_size:map_.pop(get_id(nums[i-window_size], size))# 在桶中的编号p = get_id(nums[i], size)# 在一个桶中的情况if p in map_:return True# 相邻桶情况if p-1 in map_ and abs(map_[p-1] - nums[i]) <= valueDiff:return Trueif p+1 in map_ and abs(map_[p+1] - nums[i]) <= valueDiff:return Truemap_[p] = nums[i]return False

在这里插入图片描述

数据库练习

178. 分数排名

方法2

sql

# Write your MySQL query statement below
SELECT s1.score,(SELECT COUNT(DISTINCT s2.score)FROMScores s2WHEREs2.score >= s1.score) AS 'rank'  
FROMScores s1
ORDER BY s1.score DESC

server

# Write your MySQL query statement below
SELECT s1.score,(SELECT COUNT(DISTINCT s2.score)FROMScores s2WHEREs2.score >= s1.score) AS 'rank'  
FROMScores s1
ORDER BY s1.score DESC

方法3

sql

# Write your MySQL query statement below
SELECTS.score,COUNT(DISTINCT T.score) AS 'rank'
FROMScores SLEFT JOIN Scores T ON S.score <= T.score
GROUP BYS.score, S.id
ORDER BYS.score DESC

server

/* Write your T-SQL query statement below */
SELECTS.score,COUNT(DISTINCT T.score) AS 'rank'
FROMScores SLEFT JOIN Scores T ON S.score <= T.score
GROUP BYS.score, S.id
ORDER BYS.score DESC

180. 连续出现的数字

sql

SELECT DISTINCTl1.num AS 'ConsecutiveNums'
FROMLogs l1,Logs l2,Logs l3
WHEREl1.id = l2.id - 1 AND l2.id = l3.id - 1AND l1.num = l2.numAND l2.num = l3.num

server

SELECT DISTINCTl1.num AS 'ConsecutiveNums'
FROMLogs l1,Logs l2,Logs l3
WHEREl1.id = l2.id - 1 AND l2.id = l3.id - 1AND l1.num = l2.numAND l2.num = l3.num

181. 超过经理收入的员工

sql

# Write your MySQL query statement below
SELECTe1.name AS 'Employee'
FROM Employee e1 INNER JOIN Employee e2 ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;

server

/* Write your T-SQL query statement below */
SELECTe1.name AS 'Employee'
FROM Employee e1 INNER JOIN Employee e2 ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;

182. 查找重复的电子邮箱

方法一

# Write your MySQL query statement below
SELECT email
FROM(SELECT email,COUNT(email) AS num FROMPersonGROUP BY email) AS T
WHERE num > 1

方法二

# Write your MySQL query statement below
SELECTemail
FROMPerson
GROUP BYemail
HAVINGCOUNT(email) > 1;

183. 从不订购的客户

方法一

# Write your MySQL query statement below
SELECTname AS 'Customers'
FROMCustomers
WHEREid NOT IN (SELECT customerId FROM Orders)

方法二

# Write your MySQL query statement below
SELECTname AS 'Customers'
FROMCustomers LEFT JOIN Orders ON Orders.customerId = Customers.id
WHEREcustomerId IS NULL;

部门工资最高的员工

SELECTDepartment.name AS 'Department',Employee.name AS 'Employee',Employee.salary AS 'Salary'
FROMEmployee LEFT JOIN Department ON Employee.departmentId = Department.id
WHERE(Department.id, Employee.salary) IN(SELECT departmentId, MAX(salary)FROMEmployeeGROUP BYdepartmentId)