sql中with?as用法以及with-as性能調(diào)優(yōu)/with用法舉例

更新時(shí)間：2024年01月22日 09:51:22 作者：五月天的尾巴

SQL中的WITH?AS語法是一種強(qiáng)大的工具,可以簡化復(fù)雜查詢的編寫,提高查詢的可讀性和維護(hù)性,這篇文章主要給大家介紹了關(guān)于sql中with?as用法以及with-as性能調(diào)優(yōu)/with用法的相關(guān)資料,需要的朋友可以參考下

一、概述

with as 語句是SQL中的一種常用語法，它可以為一個(gè)查詢結(jié)果或子查詢結(jié)果創(chuàng)建一個(gè)臨時(shí)表，并且可以在后續(xù)的查詢中使用這個(gè)臨時(shí)表，在查詢結(jié)束后該臨時(shí)表就被清除了。這種語法的使用可以使得復(fù)雜的查詢變得簡單，同時(shí)也可以提高查詢效率。

WITH AS短語，也叫做子查詢部分（subquery factoring），是用來定義一個(gè)SQL片斷，該SQL片斷會(huì)被整個(gè)SQL語句所用到。這個(gè)語句算是公用表表達(dá)式（CTE，Common Table Expression）。

with-as 意義：
1、對(duì)于多次反復(fù)出現(xiàn)的子查詢，可以降低掃描表的次數(shù)和減少代碼重寫，優(yōu)化性能和使編碼更加簡潔，也可以在UNION ALL的不同部分，作為提供數(shù)據(jù)的部分。
2、對(duì)于UNION ALL，使用WITH AS定義了一個(gè)UNION ALL語句，當(dāng)該片斷被調(diào)用2次以上，優(yōu)化器會(huì)自動(dòng)將該WITH AS短語所獲取的數(shù)據(jù)放入一個(gè)Temp表中。而提示meterialize則是強(qiáng)制將WITH AS短語的數(shù)據(jù)放入一個(gè)全局臨時(shí)表中。很多查詢通過該方式都可以提高速度。

with as語句支持myql、oracle、db2、hive、sql server、MariaDB、PostgreSQL等數(shù)據(jù)庫，以下列舉幾種數(shù)據(jù)庫支持的版本

mysql版本：8以及8以上的
sql server：sql server 2005以后的版本
oracle：Oracle 9i的第二版本數(shù)據(jù)庫

二、基本語法

with查詢語句不是以select開始的，而是以“WITH”關(guān)鍵字開頭,可以理解為在進(jìn)行查詢之前預(yù)先構(gòu)造了一個(gè)臨時(shí)表，之后便可多次使用它做進(jìn)一步的分析和處理。

CTE是使用WITH子句定義的，包括三個(gè)部分：CTE名稱cte_name、定義CTE的查詢語句inner_query_definition和引用CTE的外部查詢語句outer_query_definition。CTE可以在select , insert , update , delete , merge語句的執(zhí)行范圍定義。

它的格式如下：

WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)
   [,cte_name2[(column_name_list)] AS (inner_query_definition_2)]
[,...]
outer_query_definition

其中column_name_list指定inner_query_definition中的列列表名，如果不寫該選項(xiàng)，則需要保證在inner_query_definition中的列都有名稱且唯一，即對(duì)列名有兩種命名方式：內(nèi)部命名和外部命名。

注意，outer_quer_definition必須和CTE定義語句同時(shí)執(zhí)行，因?yàn)镃TE是臨時(shí)虛擬表，只有立即引用它，它的定義才是有意義的。

示例：
-- 單個(gè)子查詢
with tmp as(select username,userage from user)
select username from tmp

-- 多個(gè)子查詢  多個(gè)CTE 之間加，分割
with tmp1 as (select * from father),
     tmp2 as (select * from child)
select * from temp1,temp2 on tmp1.id = tmp2.parentId

注意：

1.必須要整體作為一條sql查詢，即with as語句后不能加分號(hào)，不然會(huì)報(bào)錯(cuò)。
2.with子句必須在引用的select語句之前定義,同級(jí)with關(guān)鍵字只能使用一次,多個(gè)只能用逗號(hào)分割；最后一個(gè)with 子句與下面的查詢之間不能有逗號(hào)，只通過右括號(hào)分割,with 子句的查詢必須用括號(hào)括起來.
3. 如果定義了with子句，但其后沒有跟使用CTE的SQL語句（如select、insert、update等），則會(huì)報(bào)錯(cuò)。
4.前面的with子句定義的查詢?cè)诤竺娴膚ith子句中可以使用。但是一個(gè)with子句內(nèi)部不能嵌套with子句
5.如果定義了with子句，而在查詢中不使用，那么會(huì)報(bào)ora-32035 錯(cuò)誤：未引用在with子句中定義的查詢名。（至少一個(gè)with查詢的name未被引用，解決方法是移除未被引用的with查詢），注意：只要后面有引用的就可以，不一定非要在主查詢中引用，比如后面的with查詢也引用了，也是可以的。
6.當(dāng)一個(gè)查詢塊名字和一個(gè)表名或其他的對(duì)象相同時(shí)，解析器從內(nèi)向外搜索，優(yōu)先使用子查詢塊名字。
7.with查詢的結(jié)果列有別名，引用的時(shí)候必須使用別名或*。

三、使用場(chǎng)景

3.1、定義CTE,并為每列重命名

mysql 8.0.34版本中測(cè)試以下sql

CREATE TABLE user(
    id INT NOT NULL PRIMARY KEY,
    sex CHAR(3),NAME CHAR(20)
);

INSERT INTO user VALUES 
(1,'nan','陳一'),
(2,'nv','珠二'),
(3,'nv','張三'),
(4,'nan','李四'),
(5,'nv','王五'),
(6,'nan','趙六');
  
# 定義CTE，順便為每列重新命名，且使用ORDER BY子句
WITH nv_user(myid,mysex,myname) AS (
    SELECT * FROM user WHERE sex='nv' ORDER BY id DESC
)
# 使用CTE
SELECT * FROM nv_user;
+------+-------+-------------+
| myid | mysex | myname      |
+------+-------+-------------+
|    5 | nv    | 王五        |
|    3 | nv    | 張三        |
|    2 | nv    | 珠二        |
+------+-------+-------------+

3.2、多次引用/多次定義

1.多次引用：避免重復(fù)書寫。

2.多次定義：避免派生表的嵌套問題。

3.可以使用遞歸CTE，實(shí)現(xiàn)遞歸查詢。

# 多次引用，避免重復(fù)書寫
WITH nv_t(myid,mysex,myname) AS (
    SELECT * FROM user WHERE sex='nv'
)
SELECT t1.*,t2.*
FROM nv_t t1 JOIN nv_t t2
WHERE t1.myid = t2.myid+1;
  
# 多次定義，避免派生表嵌套
WITH
nv_t1 AS (          /* 第一個(gè)CTE */
    SELECT * FROM user WHERE sex='nv'
),
nv_t2 AS (          /* 第二個(gè)CTE */
    SELECT * FROM nv_t1 WHERE id>3
)
SELECT * FROM nv_t2;

如果上面的語句不使用CTE而使用派生表的方式，則它等價(jià)于：

SELECT * FROM
(SELECT * FROM
(SELECT * FROM user WHERE sex='nv') AS nv_t1) AS nv_t2;

可以看到這種寫法不便于查看。

3.3、with與union all聯(lián)合使用

前面的with子句定義的查詢?cè)诤竺娴膚ith子句中可以使用

with
sql1 as (select  s_name from test_tempa),  
sql2 as (select  s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))  

select * from sql1
union all
select * from sql2
union all
select ‘no records' from dual
where not exists (select s_name from sql1 where rownum=1)  
and not exists (select s_name from sql2 where rownum=1);

3.4、with返回多種結(jié)果的值

在實(shí)際使用中我們可能會(huì)遇到需要返回多種結(jié)果的值的場(chǎng)景

-- 分類表
CREATE TABLE category ( cid VARCHAR ( 32 ) PRIMARY KEY, cname VARCHAR ( 50 ) );

-- 商品表
CREATE TABLE products (
	pid VARCHAR ( 32 ) PRIMARY KEY,
	pname VARCHAR ( 50 ),
	price INT,
	category_id VARCHAR ( 32 ),
	FOREIGN KEY ( category_id ) REFERENCES category ( cid ) 
);
-- 分類數(shù)據(jù)
INSERT INTO category(cid,cname) VALUES('c001','家電');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');
INSERT INTO category(cid,cname) VALUES('c004','汽車');

-- 商品數(shù)據(jù)
INSERT INTO products(pid, pname,price,category_id) VALUES('p001','小米電視機(jī)',5000,'c001');
INSERT INTO products(pid, pname,price,category_id) VALUES('p002','格力空調(diào)',3000,'c001');
INSERT INTO products(pid, pname,price,category_id) VALUES('p003','美的冰箱',4500,'c001');
INSERT INTO products (pid, pname,price,category_id) VALUES('p004','籃球鞋',800,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p005','運(yùn)動(dòng)褲',200,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p006','T恤',300,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p007','沖鋒衣',2000,'c002');
INSERT INTO products (pid, pname,price,category_id) VALUES('p008','神仙水',800,'c003');
INSERT INTO products (pid, pname,price,category_id) VALUES('p009','大寶',200,'c003');

如上圖，如果我想查詢“家電”中“格力空調(diào)”與“美的冰箱”的信息，不用with as寫法如下：

select * from category c
left join products p on c.cid = p.category_id
where c.cname = '家電' and p.pname in ('格力空調(diào)','美的冰箱');

使用with as寫法如下：

with c as (select * from category where cname = '家電'),
     p as (select * from products where pname in ('格力空調(diào)','美的冰箱'))
select * from c,p where c.cid = p.category_id;

②、查詢“家電”的平均價(jià)格與所有商品的最小最大值

with tem as (select avg(price) as houseElecAvg from products p
			left join category c on c.cid = p.category_id
			where c.cname = '家電'),
	tem1 as (select max(p1.price),min(p1.price) from products p1)
select * from tem,tem1;

其實(shí) WITH 表達(dá)式除了和 SELECT 一起用，還可以有下面的組合：
insert with 、with update、with delete、with with、with recursive(可以模擬數(shù)字、日期等序列)、WITH 可以定義多張表

3.5、with與insert使用

insert into table2
with
    s1 as (select rownum c1 from dual connect by rownum <= 10),
    s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where...;

四、遞歸查詢

在標(biāo)準(zhǔn)的數(shù)據(jù)庫中，如hive，Oracle，DB2，SQL SERVER，PostgreSQL都是支持 WITH AS 語句進(jìn)行遞歸查詢。mysql8.0及以上支持遞歸。

公用表表達(dá)式(CTE)具有一個(gè)重要的優(yōu)點(diǎn)，那就是能夠引用其自身，從而創(chuàng)建遞歸CTE。遞歸CTE是一個(gè)重復(fù)執(zhí)行初始CTE以返回?cái)?shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達(dá)式。

當(dāng)某個(gè)查詢引用遞歸CTE時(shí)，它即被稱為遞歸查詢。遞歸查詢通常用于返回分層數(shù)據(jù)，例如：顯示某個(gè)組織圖中的雇員或物料清單方案(其中父級(jí)產(chǎn)品有一個(gè)或多個(gè)組件，而那些組件可能還有子組件，或者是其他父級(jí)產(chǎn)品的組件)中的數(shù)據(jù)。

4.1、語法

遞歸cte中包含一個(gè)或多個(gè)定位點(diǎn)成員，一個(gè)或多個(gè)遞歸成員，最后一個(gè)定位點(diǎn)成員必須使用"union [all]"(mariadb中的遞歸CTE只支持union [all]集合算法)聯(lián)合第一個(gè)遞歸成員。

更多CTE遞歸的其他語法注意事項(xiàng)，請(qǐng)參閱遞歸公用表表達(dá)式

with recursive cte_name as (
    select_statement_1       /* 該cte_body稱為定位點(diǎn)成員 */
  union [all]
    cte_usage_statement      /* 此處引用cte自身，稱為遞歸成員 */
)
outer_definition_statement    /* 對(duì)遞歸CTE的查詢，稱為遞歸查詢 */

其中：

select_statement_1：稱為"定位點(diǎn)成員"，這是遞歸cte中最先執(zhí)行的部分，也是遞歸成員開始遞歸時(shí)的數(shù)據(jù)來源。
cte_usage_statement：稱為"遞歸成員"，該語句中必須引用cte自身。它是遞歸cte中真正開始遞歸的地方，它首先從定位點(diǎn)成員處獲取遞歸數(shù)據(jù)來源，然后和其他數(shù)據(jù)集結(jié)合開始遞歸，每遞歸一次都將遞歸結(jié)果傳遞給下一個(gè)遞歸動(dòng)作，不斷重復(fù)地查詢后，當(dāng)最終查不出數(shù)據(jù)時(shí)才結(jié)束遞歸。
outer_definition_statement：是對(duì)遞歸cte的查詢，這個(gè)查詢稱為"遞歸查詢"。

4.2、使用場(chǎng)景

4.2.1、用with遞歸構(gòu)造1-10的數(shù)據(jù)

# n迭代次數(shù)
with RECURSIVE c(n) as
 (select 1   union all select n + 1 from c where n < 10)
select n from c;

+------+
| 	 n |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

4.2.2、with與insert遞歸造數(shù)據(jù)

用 WITH 表達(dá)式來造數(shù)據(jù)，非常簡單，比如下面例子：給表 y1 添加10條記錄，日期字段要隨機(jī)。

-- 創(chuàng)建測(cè)試表
create table y1 (id serial primary key, r1 int,log_date date);

-- 插入數(shù)據(jù)
INSERT y1 (r1,log_date)
   WITH recursive tmp (a, b) AS
   (SELECT
     1,
     '2021-04-20'
   UNION
   ALL
   SELECT
     ROUND(RAND() * 10),
     b - INTERVAL ROUND(RAND() * 1000) DAY
   FROM
     tmp
   LIMIT 10)
select * from  tmp;

結(jié)果：

4.2.3、with與update更新數(shù)據(jù)

WITH recursive tmp (a, b, c) AS
    (SELECT
      1,
      1,
      '2021-04-20'
    UNION ALL
    SELECT
      a + 2,
      100,
      DATE_SUB(
        CURRENT_DATE(),
        INTERVAL ROUND(RAND() * 1000, 0) DAY
      )
    FROM
      tmp
    WHERE a < 10)
UPDATE
     tmp AS a,
     y1 AS b
   SET
     b.r1 = a.b
   WHERE a.a = b.id;

4.2.4、with與delete刪除id為奇數(shù)的行

比如刪除 ID 為奇數(shù)的行，可以用 WITH DELETE 形式的刪除語句：

WITH recursive tmp (a) AS
    (SELECT
      1
    UNION
    ALL
    SELECT
      a + 2
    FROM
      tmp
    WHERE a < 10)
    DELETE FROM y1 WHERE id IN (select * from tmp);

與 DELETE 一起使用，要注意一點(diǎn)：WITH 表達(dá)式本身數(shù)據(jù)為只讀，所以多表 DELETE 中不能包含 WITH 表達(dá)式。比如把上面的語句改成多表刪除形式會(huì)直接報(bào) WITH 表達(dá)式不可更新的錯(cuò)誤。

WITH recursive tmp (a) AS
     (SELECT
       1
     UNION
     ALL
     SELECT
       a + 2
     FROM
       tmp
     WHERE a < 100)
     delete a,b from y1 a join tmp b where a.id = b.a;

error: [HY000][1288] The target table b of the DELETE is not updatable

4.2.5、with 生成日期序列

用 WITH 表達(dá)式生成日期序列，類似于 POSTGRESQL 的 generate_series 表函數(shù)，比如，從 ‘2020-01-01’ 開始，生成一個(gè)月的日期序列：

WITH recursive seq_date (log_date) AS
      (SELECT
        '2023-07-09'
      UNION
      ALL
      SELECT
        log_date + INTERVAL 1 DAY
      FROM
        seq_date
      WHERE log_date + INTERVAL 1 DAY < '2023-07-20')
      SELECT
        log_date
      FROM
        seq_date;

+-----------+
|   log_date| 
+-----------+
| 2023-07-09|
| 2023-07-10| 
| 2023-07-11| 
| 2023-07-12| 
| 2023-07-13| 
| 2023-07-14| 
| 2023-07-15| 
| 2023-07-16| 
| 2023-07-17| 
| 2023-07-18| 
| 2023-07-19| 
+------+

WITH語句的優(yōu)點(diǎn):

(1). SQL可讀性增強(qiáng)。比如對(duì)于特定with子查詢?nèi)€(gè)有意義的名字等。

(2)、with子查詢只執(zhí)行一次，將結(jié)果存儲(chǔ)在用戶臨時(shí)表空間中，可以引用多次，增強(qiáng)性能。

舉例:在進(jìn)行導(dǎo)入EXCEL的過程中，有時(shí)候，需要將數(shù)據(jù)存儲(chǔ)在臨時(shí)表中，當(dāng)下一次在進(jìn)行導(dǎo)入的時(shí)候，進(jìn)行清除臨時(shí)表的數(shù)據(jù)，但是這時(shí)候，有時(shí)候發(fā)生并發(fā)問題的話，兩個(gè)用戶可能會(huì)分別操作對(duì)方的數(shù)據(jù)，所以，可能造成混亂，但是可以使用WITH函數(shù)和UNION語句拼接一個(gè)SQL語句，存儲(chǔ)在SESSION中，當(dāng)需要導(dǎo)出錯(cuò)誤信息的時(shí)候，可以使用該語句構(gòu)造數(shù)據(jù)。

參考文檔

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html#common-table-expressions
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
http://chabaoo.cn/database/313680ibm.htm
http://chabaoo.cn/article/236061.htm

總結(jié)

到此這篇關(guān)于sql中with as用法以及with-as性能調(diào)優(yōu)/with用法舉例的文章就介紹到這了,更多相關(guān)sql中with as用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

sql中with?as用法以及with-as性能調(diào)優(yōu)/with用法舉例

目錄

一、概述

二、基本語法

三、使用場(chǎng)景

3.1、定義CTE,并為每列重命名

3.2、多次引用/多次定義

3.3、with與union all聯(lián)合使用

3.4、with返回多種結(jié)果的值

3.5、with與insert使用

四、遞歸查詢

4.1、語法

4.2、使用場(chǎng)景

4.2.1、用with遞歸構(gòu)造1-10的數(shù)據(jù)

4.2.2、with與insert遞歸造數(shù)據(jù)

4.2.3、with與update更新數(shù)據(jù)

4.2.4、with與delete刪除id為奇數(shù)的行

4.2.5、with 生成日期序列

WITH語句的優(yōu)點(diǎn):

參考文檔

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

sql中with?as用法以及with-as性能調(diào)優(yōu)/with用法舉例

目錄

一、概述

二、基本語法

三、使用場(chǎng)景

3.1、定義CTE,并為每列重命名

3.2、多次引用/多次定義

3.3、with與union all聯(lián)合使用

3.4、with返回多種結(jié)果的值

3.5、with與insert使用

四、遞歸查詢

4.1、語法

4.2、使用場(chǎng)景

4.2.1、用with遞歸構(gòu)造1-10的數(shù)據(jù)

4.2.2、with與insert遞歸造數(shù)據(jù)

4.2.3、with與update更新數(shù)據(jù)

4.2.4、with與delete刪除id為奇數(shù)的行

4.2.5、with 生成日期序列

WITH語句的優(yōu)點(diǎn):

參考文檔

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、概述

二、基本語法

3.1、定義CTE,并為每列重命名

3.2、多次引用/多次定義

3.3、with與union all聯(lián)合使用

3.4、with返回多種結(jié)果的值

3.5、with與insert使用

四、遞歸查詢

4.1、語法

4.2、使用場(chǎng)景

4.2.1、用with遞歸構(gòu)造1-10的數(shù)據(jù)

4.2.2、with與insert遞歸造數(shù)據(jù)

4.2.3、with與update更新數(shù)據(jù)

4.2.5、with 生成日期序列