每日 18

SQL语句：
一、复杂查询
联合查询
SELECT name FROM employees WHERE dept = 'IT'
UNION
SELECT name FROM contractors WHERE role = 'Developer';
-- UNION 自动去重，UNION ALL 保留重复

子查询
-- 非关联子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 关联子查询
SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.emp_id = e.id AND s.year = 2023);

窗口函数
-- MySQL 8.0+ 支持
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank,
AVG(salary) OVER (PARTITION BY dept) as dept_avg
FROM employees;

公用表表达式
WITH regional_sales AS (
SELECT region, SUM(amount) as total
FROM orders
GROUP BY region
)
SELECT region, total FROM regional_sales WHERE total > 1000000;

二、高级 JOIN 操作
自连接 (Self Join)
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

交叉连接
-- 生成笛卡尔积
SELECT sizes.size, colors.color
FROM sizes
CROSS JOIN colors;

自然连接
-- 自动匹配相同列名
SELECT * FROM orders NATURAL JOIN customers;