MySQL SQL查询新模式CTE使用详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">前言</a></li><li><a href="#_label1">1、成果收益</a></li><li><a href="#_label2">2、背景</a></li><li><a href="#_label3">3、什么是CTE</a></li><li><a href="#_label4">4、CTE如何使用</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_0">基本语法</a></li><li><a href="#_lab2_4_1">多个 CTE 的使用</a></li><li><a href="#_lab2_4_2">示例：</a></li></ul><li><a href="#_label5">5、经验总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>前言</h2><p>本文将为开发者系统解析MySQL 8.0引入的CTE特性。通过真实业务场景案例，您将掌握：</p>
<ol><li>如何用CTE重构嵌套噩梦般的SQL语句</li><li>递归查询实现树形结构遍历的核心方法</li><li>通过查询复用提升30%以上执行效率的技巧</li><li>CTE在复杂业务场景下的最佳实践方案</li></ol>
<p class="maodian"><a name="_label1"></a></p><h2>1、成果收益</h2>
<p>本最佳实践已取得的成果或预期收益。</p>
<p>简单说，简化了复杂的SQL查询，提升了复杂查询的可读性和复用性。</p>
<p class="maodian"><a name="_label2"></a></p><h2>2、背景</h2>
<p>讲述一下问题或痛点，为什么要做这件事。</p>
<p>计费规则根据险种进行数据统计，计费规则从地区到具体险种配置，地区-计费-参保方案-参保方案详情，层级深，关系一层套一层，很多表数据需要复用，只能反反复复的查询。最后写出一个层层嵌套的SQL，看也看不懂，改也改不动。</p>
<p>传统方案面临三大难题：</p>
<ol><li><strong>嵌套黑洞</strong>：5层以上子查询导致SQL可读性断崖式下降</li><li><strong>重复炼狱</strong>：相同子查询在多个地方重复出现</li><li><strong>调试噩梦</strong>：修改一个字段需要追踪多级嵌套</li></ol>
<p class="maodian"><a name="_label3"></a></p><h2>3、什么是CTE</h2>
<p>公共表表达式（Common Table Expression，简称 CTE），CTE 是在SQL查询中定义的一个临时结果集，CTE 通常用于</p>
<ul><li><ul><li>多层嵌套子查询的简化</li><li>递归查询（如树形结构遍历，MySQL 8.0.1+支持递归CTE）</li><li>多次复用同一子查询结果</li></ul></li></ul>
<p>CTE 的定义部分类似于创建一个临时视图，但其生命周期仅限于当前查询。通过 <code>WITH</code> 创建临时命名结果集，<code>WITH</code> 子句更轻量，适合在单个查询中复用中间结果，提升复杂查询的可读性和复用性。</p>
<p class="maodian"><a name="_label4"></a></p><h2>4、CTE如何使用</h2>
<p class="maodian"><a name="_lab2_4_0"></a></p><h3>基本语法</h3>
<div class="jb51code"><pre class="brush:sql;">WITH cte_name AS (
-- 子查询
SELECT ...
)
SELECT ...
FROM cte_name;</pre></div>
<ul><li><code>cte_name</code>：CTE 的名称，就是临时表的名称。</li><li>子查询：定义 CTE 的结果集。</li><li>后续查询：可以引用 CTE 名称。</li></ul>
<p>注意with是不需要分号结尾的，当分号出现时就意味着当前的SQL生命周期已经结束了。</p>
<p class="maodian"><a name="_lab2_4_1"></a></p><h3>多个 CTE 的使用</h3>
<p>可以在一个 <code>WITH</code> 子句中定义多个 CTE，用逗号分隔。</p>
<div class="jb51code"><pre class="brush:sql;">WITH
cus_totals AS (
SELECT cus_id, SUM(amount) AS total_amount
FROM orders
GROUP BY cus_id
),
latest_orders AS (
SELECT cus_id, MAX(order_date) AS latest_date
FROM orders
GROUP BY cus_id
)
SELECT t.cus_id, t.total_amount, l.latest_date
FROM cus_totals t
JOIN latest_orders l ON t.cus_id = l.cus_id;</pre></div>
<p class="maodian"><a name="_lab2_4_2"></a></p><h3>示例：</h3>
<ol><li>第一个 CTE：计算每个客户的订单总金额。</li><li>第二个 CTE：计算每个客户的最新订单日期。</li><li>最终查询：连接两个 CTE，生成结果。</li></ol>
<p class="maodian"><a name="_label5"></a></p><h2>5、经验总结</h2>
<p>主要用于数据导出的场景，更方便多表查询的场景。</p>
頁:
[1]