SQL 复习 02-CSDN博客

本文链接：https://blog.csdn.net/weixin_41311624/article/details/129550641

这篇文章展示了如何使用SQL进行复杂的数据分析，包括使用窗口函数进行行排名，通过子查询处理不规则数据，以及进行多表连接和分组操作。示例涵盖了从基础的SELECT语句到高级的分析技巧，适合数据库管理和数据分析师学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://github.com/datawhalechina/team-learning-sql/blob/main/Task06：综合练习.md

/* Answer 01 */

WITH t2 AS (
	WITH t1 AS(
		SELECT 
		a.Name AS Employee,
		a.Salary AS Salary,
		b.Name AS Department
		FROM employee AS a JOIN department AS b
		WHERE a.DepartmentId = b.Id
	)
    SELECT 
	*, 
	RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS ranking
	FROM t1
)
SELECT 
Department, Employee, Salary
FROM t2
WHERE ranking = 1;

/* Answer 02 */

SELECT 
(
CASE
WHEN id <> (select count(id) from seat) AND mod(id, 2) <> 0 THEN id + 1
WHEN id <> (select count(id) from seat) AND mod(id, 2) = 0 THEN id - 1
ELSE id
END
) 
AS id,
student
FROM seat
ORDER BY id;

/* Answer 03*/

SELECT 
Score,
RANK() OVER(ORDER BY amount DESC) AS `Rank`
FROM Score;

/* Answer 04 */

SELECT DISTINCT(Num) AS ConsecutiveNums 
FROM 
(
	SELECT 
	*,
	lag(id, 2) OVER(PARTITION BY Num ORDER BY id) AS id_new 
	FROM logs
) AS A 
WHERE A.id = A.id_new + 2;

-- LAG 函数语法：
-- LAG(return_value ,offset [,default]) 
-- OVER (
--     [PARTITION BY partition_expression, ... ]
--     ORDER BY sort_expression [ASC | DESC], ...
-- )

/* Answer 05 */

SELECT id,
CASE
WHEN t.p_id IS NULL THEN 'Root' 
WHEN t.id IN(SELECT p_id FROM tree ) THEN 'Inner'
ELSE 'Leaf' 
END AS `Type`
FROM  tree t;

/* Answer 06 */

SELECT 
A.Name
FROM Employee AS A
LEFT JOIN Employee AS B
ON A.Id = B.ManagerId
GROUP BY A.Id
HAVING COUNT(*) >=  5

/* Answer 07 */

-- 待续