LeetCode Database题解

时间:2022-09-02 00:04:22

175. Combine Two Tables

使用外连接即可。

# Write your MySQL query statement below
select FirstName, LastName, City, State from Person left outer join Address on Person.PersonId = Address.PersonId; # Write your MySQL query statement below
select p.FirstName, p.LastName, a.City, a.State from Person as p left outer join Address as a on p.PersonId = a.PersonId;

176. Second Highest Salary

查询第二高工资值,可能无此值。使用聚集函数与子查询即可,注意MAX可能返回NULL。

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee where Salary < (select max(Salary) from Employee); # Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee where Salary not in(select max(Salary) from Employee);

177. Nth Highest Salary

查询第N高工资值,可能无此值。使用IFNULL,然后再对结果进行限制即可。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N - 1;
RETURN (
# Write your MySQL query statement below.
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1), NULL)
);
END

178. Rank Scores

按得分排名并计算排名。使用子查询、聚集函数,然后再对结果进行排序即可。

# Write your MySQL query statement below
select Scores.Score, (select count(*) from (select distinct Score from Scores) as DistinctScores where DistinctScores.Score >= Scores.Score) as Rank from Scores order by Score desc; # Write your MySQL query statement below
select Scores.Score, count(DistinctScores.Score) as Rank from Scores, (select distinct Score from Scores) as DistinctScores where Scores.Score <= DistinctScores.Score group by Scores.Id, Scores.Score order by Scores.Score desc;

180. Consecutive Numbers

找出至少连续出现3次的Num。先排序,再查询即可。也有人使用变量来解答。

# Write your MySQL query statement below
select distinct(l1.Num) as ConsecutiveNums from Logs l1, Logs l2, Logs l3 where l1.Id + 1 = l2.Id and l1.Id + 2 = l3.Id and l1.Num = l2.Num and l1.Num = l3.Num;

181. Employees Earning More Than Their Managers

查询工资比上司高的员工。使用自连接即可。

# Write your MySQL query statement below
select e1.Name as Employee from Employee as e1, Employee as e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary;

182. Duplicate Emails

查找表中有重复的emails。使用数据分组或自连接即可。

# Write your MySQL query statement below
select Email from Person group by Email having count(*) > 1; # Write your MySQL query statement below
select distinct(p1.Email) from Person as p1, Person as p2 where p1.Id != p2.Id and p1.Email = p2.Email;

183. Customers Who Never Order

查出没有订单的客户。使用数据过滤即可。

# Write your MySQL query statement below
select Name as Customers from Customers where Customers.Id not in (select distinct(CustomerId) from Orders);

184. Department Highest Salary

查询每个部内最高的工资,最高工资可能有多个员工。这题相对来说稍复杂,使用子查询与数据分组来解决。

# Write your MySQL query statement below
select Department.Name as Department, Employee.Name as Employee, max(Salary) as Salary from Employee, Department where Employee.DepartmentId = Department.Id;
# 错误解法,结果会出现[null, null, null]这种情况 # Write your MySQL query statement below
select Department.Name as Department, Employee.Name as Employee, Salary from Employee, Department where Employee.DepartmentId = Department.Id order by Salary limit 1;
# 错误解法,结果只会有一行 # Write your MySQL query statement below
select d.Name as Department, e.Name as Employee, e.Salary as Salary
from Department as d, (select Name, max(Salary) as Salary, DepartmentId from Employee group by DepartmentId) as e
where d.Id = e.DepartmentId;
# 错误解法,当同一部门有两个最高的Salary时候,结果只有一行,故必须使用下面解法,使用2个Employee表 # Write your MySQL query statement below
select d.Name as Department, e.Name as Employee, e.Salary as Salary
from Department as d, Employee as e, (select max(Salary) as Salary, DepartmentId from Employee group by DepartmentId) as h
where d.Id = h.DepartmentId and e.Salary = h.Salary and e.DepartmentId = h.DepartmentId;

LeetCode Database题解的更多相关文章

  1. LeetCode OJ 题解

    博客搬至blog.csgrandeur.com,cnblogs不再更新. 新的题解会更新在新博客:http://blog.csgrandeur.com/2014/01/15/LeetCode-OJ-S ...

  2. Leetcode 简略题解 - 共567题

    Leetcode 简略题解 - 共567题     写在开头:我作为一个老实人,一向非常反感骗赞.收智商税两种行为.前几天看到不止两三位用户说自己辛苦写了干货,结果收藏数是点赞数的三倍有余,感觉自己的 ...

  3. LeetCode 算法题解 js 版 &lpar;001 Two Sum&rpar;

    LeetCode 算法题解 js 版 (001 Two Sum) 两数之和 https://leetcode.com/problems/two-sum/submissions/ https://lee ...

  4. leetcode &amp&semi; lintcode 题解

    刷题备忘录,for bug-free 招行面试题--求无序数组最长连续序列的长度,这里连续指的是值连续--间隔为1,并不是数值的位置连续 问题: 给出一个未排序的整数数组,找出最长的连续元素序列的长度 ...

  5. LeetCode一句话题解

    深度优先搜索 人生经验 1. 需要输出所有解.并由于元素集有重复元素,要求返回的结果需要去重的情况,可考虑使用值对应数量的map,然后分别考虑依次取不同数量该值的可能. LeetCode39 题目:给 ...

  6. &lbrack;leetcode&rsqb; 位操作题解

    子集 题目[78]:给定一组不含重复元素的整数数组 nums,返回该数组所有可能的子集(幂集). 示例: 输入: nums = [1,2,3] 输出: [ [3],   [1],   [2],   [ ...

  7. LeetCode 中等题解(3)

    34 在排序数组中查找元素的第一个和最后一个位置 Question 给定一个按照升序排列的整数数组 nums,和一个目标值 target.找出给定目标值在数组中的开始位置和结束位置. 你的算法时间复杂 ...

  8. LeetCode 中等题解(1)

    16 最接近的三数之和 Question 给定一个包括 n 个整数的数组 nums 和 一个目标值 target.找出 nums 中的三个整数,使得它们的和与 target 最接近.返回这三个数的和. ...

  9. leetcode database题目

    LeetCode有10道SQL的题目,最近学习SQL语言,顺便刷题强化一下, 说实话刷完SQL学习指南这本书,不是很难,上面的例子 跟语法规则我都能理解透, 实际中来做一些比较难的业务逻辑题,却一下子 ...

随机推荐

  1. 总结一下Android中主题&lpar;Theme&rpar;的正确玩法

    在AndroidManifest.xml文件中有<application android:theme="@style/AppTheme">,其中的@style/AppT ...

  2. JavaScript中concat()方法

    concat(参数) 用于数组之间的连接, arrayObject.concat(arrayX,arrayX,......,arrayX) 如: var a = [1,2,3]; document.w ...

  3. Recover Binary Search Tree

    Two elements of a binary search tree (BST) are swapped by mistake. Recover the tree without changing ...

  4. HTML5火焰文字特效DEMO演示---转载

    只有google支持 <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> &l ...

  5. String详解&comma; String和CharSequence区别&comma; StringBuilder和StringBuffer的区别 &lpar;String系列之1&rpar;

    本章主要介绍String和CharSequence的区别,以及它们的API详细使用方法. 转载请注明出处:http://www.cnblogs.com/skywang12345/p/string01. ...

  6. NSNotification系统通知优化

    最近在github上看到了LRNotificationObserver这个项目,看了一下实现方式,作者通过ARC机制实例化注册对象子类与关联对象的方法来管理注册对象的生命周期.从而省去了系统通知移除的 ...

  7. java&lowbar;method&lowbar;MD5加密

    /** * @param Original String * @return Encrypted String */ public String Md5(String plainText ) { tr ...

  8. SQL用replace替换文本部分内容

    替换文本内容 update tk_question set stem=replace(cast(stem as varchar(max)) ,'被替换文本','替换文本') 查询字段内容长度 sele ...

  9. MBProgressHUD 问题

    1.MBProgressHUD *HUD = [[MBProgressHUD alloc] initWithWindow:[UIApplication sharedApplication].keyWi ...

  10. usaco training 4&period;1&period;3 fence6 题解

    Fence Loops题解 The fences that surround Farmer Brown's collection of pastures have gotten out of cont ...