LeetCode|SQL
select P.FirstName,P.LastName,A.City,A.State
FROM Person P left join Address A ON P.personId = A.personId;
//第二高薪水
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
order by Salary DESC limit 1,1),NULL)as 'SecondHighestSalary';
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N,1),NULL) as NthHighestSalay
);
END//分数排名
# Write your MySQL query statement below
SELECT Score,
DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores//连续出现三次的数字SELECT DISTINCT l1.Num AS 'ConsecutiveNums'
FROM Logs l1,Logs l2,Logs l3
WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num;
// 重复邮箱SELECT Email FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;
#部门工资最高的员工
SELECT d.name AS 'Department', e.name AS 'Employee', e.Salary
FROM Department d join Employee e
ON d.Id = e.DepartmentId
WHERE (e.DepartmentId,e.Salary) IN
(
SELECT DepartmentId,MAX(Salary)
FROM Employee
GROUP BY DepartmentId
);
【LeetCode|SQL】
推荐阅读
- 【Leetcode/Python】001-Two|【Leetcode/Python】001-Two Sum
- leetcode|leetcode 92. 反转链表 II
- 二叉树路径节点关键值和等于目标值（LeetCode--112&LeetCode--113）
- py连接mysql
- 2019-01-18Mysql中主机名的问题
- MySql数据库备份与恢复
- LeetCode算法题-11.|LeetCode算法题-11. 盛最多水的容器（Swift）
- LeetCode（03）Longest|LeetCode（03）Longest Substring Without Repeating Characters
- mysql|InnoDB数据页结构
- 数据库|SQL行转列方式优化查询性能实践