[TOC] ## WITH 概述 - WITH提供了一种方式来书写在一个大型查询中使用的辅助语句,这些语句通常被称为公共表表达式或CTE - WITH查询的一个有用的特性是在每一次父查询的执行中它们通常只被计算一次 ### WITH 示例:显示在高销售区域每种产品的销售总额 ``` WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product; ``` ### 递归 WITH ``` WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) ``` 由于递归语句可能存在无限递归,可使用 limit 当时进行限制 ``` SELECT n FROM t LIMIT 100; ```