SQL难点突破之复杂业务逻辑的SQL查询实战

复杂业务逻辑SQL查询的核心挑战

处理多表关联、聚合计算、子查询嵌套时容易出现性能瓶颈和逻辑混乱，需掌握分层构建和优化技巧。

多表关联的精准控制

使用显式JOIN替代隐式连接，明确关联条件避免笛卡尔积。通过索引优化关联字段，例如为外键添加B-Tree索引。对于大型表关联，考虑先过滤再关联：

SELECT o.order_id, c.customer_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.create_time > '2023-01-01'  -- 先筛选订单再关联

分层聚合与临时结果集

复杂统计分阶段计算，利用CTE (Common Table Expression) 或临时表分解逻辑。统计每月订单总金额及TOP3客户：

WITH monthly_stats AS (SELECT DATE_TRUNC('month', order_date) AS month,customer_id,SUM(amount) AS total_amountFROM ordersGROUP BY 1, 2
)
SELECT month, customer_id, total_amount,RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) AS rank
FROM monthly_stats
QUALIFY rank <= 3

窗口函数的进阶应用

处理行间关系时使用窗口函数避免自连接。计算移动平均或累计占比：

SELECT product_id,sale_date,daily_sales,AVG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS 6 PRECEDING) AS 7day_avg
FROM sales

动态条件构建技巧

使用CASE WHEN实现条件聚合，处理多维度指标计算。统计不同价格区间的商品数量：

SELECT category,COUNT(CASE WHEN price < 100 THEN 1 END) AS low_price,COUNT(CASE WHEN price BETWEEN 100 AND 500 THEN 1 END) AS mid_price
FROM products
GROUP BY category

递归查询解决层次结构

处理树形数据时使用递归CTE，例如组织架构或分类层级查询：

WITH RECURSIVE org_tree AS (SELECT id, name, parent_id, 1 AS levelFROM organizationWHERE parent_id IS NULLUNION ALLSELECT o.id, o.name, o.parent_id, t.level + 1FROM organization oJOIN org_tree t ON o.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level

执行计划分析与优化

通过EXPLAIN ANALYZE识别性能瓶颈，关注全表扫描和排序操作。对于大表分页使用延迟关联：

SELECT t.* 
FROM table t
JOIN (SELECT id FROM table WHERE condition ORDER BY col LIMIT 10000, 10) tmp
ON t.id = tmp.id