1. 查詢不在表里的數據，一張學生表，一張學生的選課表，要求查出沒有選課的學生？

select students.student_name from students left join course_selection on students.student_id=course_selection.student_id where course_selection.student_id is null

2. 查找第N高的數據，查找課程編號為”01“的成績第三高的學生，如果不存在則返回null

select IFNULL((select scores.score from scores order by scores.score desc limit 1 offset 2) ,null) as "第三高的成績"

3. 分組排序，按成績從大到小排序如80，80，76，70，50 對應的排序為1，1，3，4，5

select  *,RANK() over(ORDER BY scores.score DESC) as "排名" from scores

4. 連續出現N次問題，學生連續3個學號相鄰的學生出現年齡相同的年齡

select distinct a.age from students a,students b,students c where a.student_id=b.student_id+1 and b.student_id=c.student_id+1 and a.age=b.age and b.age=c.age 

常見知識點：

1. 多層嵌套子查詢 + 聚合函數

題目：查詢訂單總額高于該客戶平均訂單金額的所有訂單

SELECT order_id, customer_id, total_amount
FROM orders o1
WHERE total_amount > (SELECT AVG(total_amount)FROM orders o2WHERE o2.customer_id = o1.customer_id
);

考點：關聯子查詢、聚合函數、比較運算符

---

2. 多表JOIN + 分組統計

題目：查詢每個部門的最高薪員工信息

SELECT d.dept_name, e.emp_name, e.salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
WHERE e.salary = (SELECT MAX(salary)FROM employeesWHERE dept_id = d.dept_id
);

考點：內連接、相關子查詢、分組極值

---

3. 窗口函數應用

題目：查詢每個部門薪資排名前三的員工

SELECT dept_id, emp_name, salary 
FROM (SELECT *,DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rkFROM employees
) t
WHERE rk <= 3;

考點：窗口函數、排名函數、子查詢

---

4. 遞歸查詢層級數據

題目：查詢某員工的所有下級（包含N級）

WITH RECURSIVE emp_tree AS (SELECT emp_id, emp_name, manager_idFROM employeesWHERE emp_id = 1001  -- 指定上級IDUNION ALLSELECT e.emp_id, e.emp_name, e.manager_idFROM employees eJOIN emp_tree et ON e.manager_id = et.emp_id
)
SELECT * FROM emp_tree;

考點：CTE遞歸查詢、樹形結構處理

---

5. 行轉列動態查詢

題目：動態生成各月銷售額的列式報表

SET @sql = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('SUM(CASE WHEN MONTH(order_date) = ',month,' THEN amount ELSE 0 END) AS `',month_name, '`')) INTO @sql
FROM (SELECT MONTH(order_date) month, DATE_FORMAT(order_date, '%b') month_nameFROM ordersGROUP BY 1,2
) m;SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM orders GROUP BY product_id');PREPARE stmt FROM @sql;
EXECUTE stmt;

考點：動態SQL、PIVOT轉換、GROUP_CONCAT函數

---

6. 復雜日期處理

題目：查詢連續3天登錄的用戶

SELECT DISTINCT a.user_id
FROM login_log a
JOIN login_log b ON a.user_id = b.user_id AND b.login_date = DATE_ADD(a.login_date, INTERVAL 1 DAY)
JOIN login_log c ON a.user_id = c.user_id AND c.login_date = DATE_ADD(a.login_date, INTERVAL 2 DAY);

考點：日期函數、自連接、連續性問題

---

7. 存在性檢查

題目：查詢購買了所有品類商品的客戶

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT category_id) = (SELECT COUNT(DISTINCT category_id) FROM products
);

考點：HAVING子句、集合運算、全量存在判斷

---

8. 分頁性能優化

題目：高效實現千萬級數據分頁

SELECT id, name, create_time
FROM large_table
WHERE create_time > '2023-01-01'
ORDER BY create_time DESC, id DESC
LIMIT 10 OFFSET 100000;

考點：分頁優化、索引設計、排序字段選擇

---

9. 多重條件聚合

題目：統計各商品不同價格區間的銷量

SELECT product_id,SUM(CASE WHEN price < 100 THEN 1 ELSE 0 END) AS low_price,SUM(CASE WHEN price BETWEEN 100 AND 500 THEN 1 ELSE 0 END) AS mid_price,SUM(CASE WHEN price > 500 THEN 1 ELSE 0 END) AS high_price
FROM orders
GROUP BY product_id;

考點：條件聚合、CASE表達式

---

10. 數據去重保留最新

題目：刪除重復訂單（保留最新記錄）

DELETE FROM orders
WHERE order_id NOT IN (SELECT MAX(order_id)FROM ordersGROUP BY customer_id, product_id, order_date
);

考點：數據去重、保留極值、子查詢刪除

---

11. 多結果集合并

題目：合并新老系統用戶表（去重）

SELECT user_id, user_name FROM old_users
UNION 
SELECT user_id, user_name FROM new_users;

考點：集合操作、UNION去重

---

12. 事務并發控制

題目：實現庫存安全扣減

START TRANSACTION;
SELECT stock FROM products WHERE id=1001 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id=1001;
COMMIT;

考點：事務隔離、悲觀鎖、并發控制

---