[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; ```