https://leetcode.com/problems/combine-two-tables/description/
Person 表:
Column Name
Type
PersonId
int
FirstName
varchar
LastName
varchar
Address 表:
Column Name
Type
AddressId
int
PersonId
int
City
varchar
State
varchar
查找 FirstName, LastName, City, State 数据,而不管一个用户有没有填地址信息。
SELECT
FirstName,
LastName,
City,
State
FROM
Person P
LEFT JOIN Address A
ON P.PersonId = A.PersonId;
https://leetcode.com/problems/employees-earning-more-than-their-managers/description/
Employee 表:
Id
Name
Salary
ManagerId
1
Joe
70000
3
2
Henry
80000
4
3
Sam
60000
NULL
4
Max
90000
NULL
查找薪资大于其经理薪资的员工信息。
SELECT
E1.NAME AS Employee
FROM
Employee E1
INNER JOIN Employee E2
ON E1.ManagerId = E2.Id
AND E1.Salary > E2.Salary;
https://leetcode.com/problems/customers-who-never-order/description/
Curstomers 表:
Id
Name
1
Joe
2
Henry
3
Sam
4
Max
Orders 表:
Id
CustomerId
1
3
2
1
查找没有订单的顾客信息:
Customers
Henry
Max
(1)子查询
SELECT
Name AS Customers
FROM
Customers
WHERE
Id NOT IN (SELECT CustomerId FROM Orders)
(2)
SELECT
C.Name AS Customers
FROM
Customers C
LEFT JOIN Orders O
ON C.Id = O.CustomerId
WHERE
O.CustomerId IS NULL;
https://leetcode.com/problems/department-highest-salary/description/
Employee 表:
Id
Name
Salary
DepartmentId
1
Joe
70000
1
2
Henry
80000
2
3
Sam
60000
2
4
Max
90000
1
Department 表:
Id
Name
1
IT
2
Sales
查找一个 Department 中收入最高者的信息:
Department
Employee
Salary
IT
Max
90000
Sales
Henry
80000
SELECT
D.NAME Department,
E.NAME Employee,
E.Salary
FROM
Employee E,
Department D,
( SELECT DepartmentId, MAX( Salary ) Salary FROM Employee GROUP BY DepartmentId ) M
WHERE
E.DepartmentId = D.Id
AND E.DepartmentId = M.DepartmentId
AND E.Salary = M.Salary;
https://leetcode.com/problems/second-highest-salary/description/
Id
Salary
1
100
2
200
3
300
查找工资第二高的员工。
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
https://leetcode.com/problems/nth-highest-salary/
Description
查找工资第 N 高的员工。
CREATE FUNCTION getNthHighestSalary ( N INT )
RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N, 1 )
);
END
https://leetcode.com/problems/rank-scores/description/
Id
Score
1
3.50
2
3.65
3
4.00
4
3.85
5
4.00
6
3.65
将得分排序,并统计排名。
Score
Rank
4.00
1
4.00
1
3.85
2
3.65
3
3.65
3
3.50
4
(1)解法一:对于每一个分数,从表中找出有多少个大于或等于该分数的不重复分数,然后降序排列
select Score,
(select count(distinct Score) from Scores s2 where s2.Score >= s1.Score) Rank
from Scores s1
order by Score DESC;
(2)解法二:使用联结 join,条件是左表的分数小于等于右表的分数时,对右表的分数进行计数(即计算有几个不重复的分数大于自己,计算结果就是rank),然后根据id分组后,再根据分数降序排列
select s1.Score, count(distinct s2.Score) Rank
from Scores as s1 inner join Scores as s2 on s1.Score <= s2.Score
group by s1.Id
order by s1.Score DESC;
https://leetcode.com/problems/consecutive-numbers/description/
数字表:
Id
Num
1
1
2
1
3
1
4
2
5
1
6
2
7
2
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
ConsecutiveNums
1
(1)用l1分别和l2, l3内交,l1和l2的Id下一个位置比,l1和l3的下两个位置比,然后将Num都相同的数字返回即可
select distinct l1.Num ConsecutiveNums from Logs l1
join Logs l2 on l1.Id = l2.Id - 1
join Logs l3 on l1.Id = l3.Id - 2
where l1.Num = l2.Num and l2.Num = l3.Num;
(2)直接在三个表的实例中查找,然后把四个条件限定上,就可以返回正确结果了 ??
SELECT DISTINCT l1.Num 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;
https://leetcode.com/problems/exchange-seats/description/
seat 表存储着座位对应的学生。
id
student
1
Abbot
2
Doris
3
Emerson
4
Green
5
Jeames
要求交换相邻座位的两个学生,如果最后一个座位是奇数,那么不交换这个座位上的学生。
id
student
1
Doris
2
Abbot
3
Green
4
Emerson
5
Jeames
对照上表及其查询结果可以得知,当原id为奇数时,交换座位后的id变为id+1,当原id为偶数时,交换座位后的id变为id-1,另一个方面需要考虑的是,学生人数为奇数时,最后一个学生的id不变,故应当用子查询确定学生的人数,然后分情况讨论即可
select (case
when mod(id,2)!=0 and id!=counts then id+1
when mod(id,2)!=0 and id=counts then id
else id-1 end)as id,student
from seat,(select count(*)as counts from seat)as seat_counts
order by id;
手机扫一扫
移动阅读更方便
你可能感兴趣的文章