SQL 解题模板

适用场景

多表查询、聚合统计、排名、连续问题

常用模式

JOIN

 
-- LEFT JOIN：保留左表所有行
 
SELECT p.FirstName, p.LastName, a.City, a.State
 
FROM Person p
 
LEFT JOIN Address a ON p.PersonId = a.PersonId;
 

排名窗口函数

 
-- DENSE_RANK：不跳号排名
 
SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
 
FROM Scores;
 
-- ROW_NUMBER：行号
 
-- RANK：跳号排名
 

TOP N

 
-- 第 N 高
 
SELECT DISTINCT salary
 
FROM Employee
 
ORDER BY salary DESC
 
LIMIT 1 OFFSET N-1;
 

子查询

 
-- 部门最高工资
 
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
 
FROM Employee e
 
JOIN Department d ON e.departmentId = d.id
 
WHERE (e.departmentId, e.salary) IN (
 
    SELECT departmentId, MAX(salary)
 
    FROM Employee
 
    GROUP BY departmentId
 
);
 

关键要点

• JOIN 分清 INNER/LEFT/RIGHT

• 窗口函数解决排名类问题

• 连续问题用自连接

• 分组取前 N：相关子查询或窗口函数

→ 查看该分类题目：LeetCode学习路线图 > 十六、SQL高频