AS 用法:

AS在mysql用來(lái)給列/表起別名.

有時(shí)，列的名稱是一些表達(dá)式，使查詢的輸出很難理解。要給列一個(gè)描述性名稱，可以使用列別名。

要給列添加別名，可以使用AS關(guān)鍵詞后跟別名

例子1:

SELECT 
 [column_1 | expression] AS col_name
FROM table_name;

如果別名包含空格，則必須引用以下內(nèi)容：

例子2:

SELECT 
 [column_1 | expression] AS 'col name'
FROM table_name;

with(Common Table Expressions/CTE)用法:

with在mysql中被稱為公共表達(dá)式,可以作為一個(gè)臨時(shí)表然后在其他結(jié)構(gòu)中調(diào)用.如果是自身調(diào)用那么就是后面講的遞歸.

語(yǔ)法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表達(dá)式的名稱,可以理解為表名,用來(lái)表示as后面跟著的子查詢

col_name :公共表達(dá)式包含的列名,可以寫(xiě)也可以不寫(xiě)

例子1:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

例子2:

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

例子3:

這里的第一個(gè)as后面接的是子查詢,第二個(gè)as表示列名,而不是子查詢.

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

with的合法用法:

在子查詢(包括派生的表子查詢)的開(kāi)始處

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

同一級(jí)別只允許一個(gè)WITH子句。同一級(jí)別的WITH后面跟著WITH是不允許的,下面是非法用法:

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

改為合法用法:

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

在這里面as代表列名,sql不是順序執(zhí)行的,這一點(diǎn)了解的話就很好理解這個(gè)as了

簡(jiǎn)單遞歸用法:

首先我們引出一個(gè)問(wèn)題: 什么叫做遞歸?

遞歸:給定函數(shù)初始條件,然后反復(fù)調(diào)用自身直到終止條件.

例子1:遞歸得到依次遞增的序列:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

運(yùn)行結(jié)果:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

官方文檔中對(duì)于這個(gè)寫(xiě)法的解釋:

At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set (1) and produces 1+1=2; the second iteration operates on the first iteration’s row set (2) and produces 2+1=3; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.

用python實(shí)現(xiàn)就是:

def cte(n):
	print(n)
	if n<5:
		cte(n+1)

也就是說(shuō),一個(gè)with recursive 由兩部分組成.第一部分是非遞歸部分(union all上方),第二部分是遞歸部分(union all下方).遞歸部分第一次進(jìn)入的時(shí)候使用非遞歸部分傳遞過(guò)來(lái)的參數(shù),也就是第一行的數(shù)據(jù)值,進(jìn)而得到第二行數(shù)據(jù)值.然后根據(jù)第二行數(shù)據(jù)值得到第三行數(shù)據(jù)值.

例子2:遞歸得到不斷復(fù)制的字符串

這里的as表示列名,表示說(shuō)這個(gè)CTE有兩個(gè)列,也可以寫(xiě)為with cte(n,str) as (subquery)

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

結(jié)果:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

這里的話concat是每一次都連接一個(gè)str,這個(gè)str來(lái)自上一行的結(jié)果,但是最終輸出卻是每一行都沒(méi)有變化的值,這是為什么?
這是因?yàn)槲覀冊(cè)诼暶鱯tr的時(shí)候限制了它的字符長(zhǎng)度,使用 類型轉(zhuǎn)換CAST(‘abc’ AS CHAR(30)) 就可以得到復(fù)制的字符串了.
**注意:**這里也可能會(huì)報(bào)錯(cuò),看mysql模式.在嚴(yán)格模式下這里會(huì)顯示Error Code: 1406. Data too long for column 'str' at row 1

關(guān)于strict SQL mode和nonstrict SQL mode:mysql 嚴(yán)格模式 Strict Mode說(shuō)明

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

當(dāng)然,如果上一行的值有多個(gè),我們還可以對(duì)多個(gè)值進(jìn)行重新組合得到我們想要的結(jié)果,比如下面這個(gè)例子.

例子3:生成斐波那契數(shù)列

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

結(jié)果:

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

語(yǔ)法說(shuō)明:

UNION ALL與UNION DISTINCT UNION ALL:

• UNION ALL:
  非遞歸部分和遞歸部分用UNION ALL分隔,那么所有的行都會(huì)被加入到最后的表中
• UNION DISTINCT:
  非遞歸部分和遞歸部分用UNION DISTINCT分隔，重復(fù)的行被消除。這對(duì)于執(zhí)行傳遞閉包的查詢非常有用，以避免無(wú)限循環(huán)。

limit控制遞歸次數(shù)

recursive(第二個(gè)select)不能使用的結(jié)構(gòu):

官網(wǎng)的描述:

The recursive SELECT part must not contain these constructs:

Aggregate functions such as SUM()

Window functions

GROUP BY

ORDER BY

DISTINCT

限制遞歸次數(shù)/時(shí)間:

當(dāng)出現(xiàn)不符合設(shè)置情況的會(huì)報(bào)錯(cuò),分為以下幾種設(shè)置方法:

• cte_max_recursion_depth :default 設(shè)置為1000,表達(dá)遞歸的層數(shù).可以使用如下語(yǔ)句修改這個(gè)值:

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

當(dāng)然也可以設(shè)置為global,也就是set global cte_max_recursion_depth = 1000000;這樣子就對(duì)全局的遞歸都有限制

• max_execution_time :設(shè)置最近的遞歸時(shí)間

SET max_execution_time = 1000; -- impose one second timeout

• MAX_EXECUTION_TIME:設(shè)置全局的遞歸時(shí)間

官網(wǎng)文檔說(shuō)明如下:

• The cte_max_recursion_depth system variable enforces a limit on the
  number of recursion levels for CTEs. The server terminates execution
  of any CTE that recurses more levels than the value of this variable.
• The max_execution_time system variable enforces an execution timeout
  for SELECT statements executed within the current session.
• The MAX_EXECUTION_TIME optimizer hint enforces a per-query execution
  timeout for the SELECT statement in which it appears.
• limit:限之最大行的數(shù)量

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

補(bǔ)充：MySql8使用WITH RECURSIVE進(jìn)行遞歸查詢下級(jí)節(jié)點(diǎn)數(shù)據(jù)

#查詢id=62的所有子節(jié)點(diǎn)
WITH RECURSIVE temp AS (
		SELECT * FROM tbsys_office o WHERE o.id=62
		UNION ALL
		SELECT o.* FROM tbsys_office o,temp t WHERE t.id=o.parent_id
) SELECT * FROM temp;

#查詢id=80的所有父節(jié)點(diǎn)
WITH RECURSIVE temp AS (
		SELECT * FROM tbsys_office o WHERE o.id=80
		UNION ALL
		SELECT o.* FROM tbsys_office o,temp t WHERE t.parent_id=o.id
) SELECT * FROM temp;

總結(jié)

到此這篇關(guān)于mysql遞歸函數(shù)with recursive用法的文章就介紹到這了,更多相關(guān)mysql遞歸函數(shù)with recursive內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論