MySQL-WITH AS用法
如果一整句查詢中
多個子查詢都需要使用同一個子查詢
的結果,那麼就可以用with as,將共用的子查詢提取出來,加個別名。後面查詢語句可以直接用,對於大量複雜的SQL語句起到了很好的最佳化作用。
注意:
相當於一個臨時表,但是不同於檢視,不會儲存起來,要與select配合使用。
同一個select前可以有多個臨時表,寫一個with就可以,用逗號隔開,最後一個with語句不要用逗號。
with子句要用括號括起來。
為了更直觀我們先建立兩個表,準備一些資料:
—— 分類表
CREATE
TABLE
category
(
cid
VARCHAR
(
32
)
PRIMARY
KEY
,
cname
VARCHAR
(
50
)
);
—— 商品表
CREATE
TABLE
products
(
pid
VARCHAR
(
32
)
PRIMARY
KEY
,
pname
VARCHAR
(
50
),
price
INT
,
flag
VARCHAR
(
2
),
—— 是否上架標記為:1表示上架、0表示下架
category_id
VARCHAR
(
32
),
FOREIGN
KEY
(
category_id
)
REFERENCES
category
(
cid
)
);
—— 分類資料
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’
,
‘汽車’
);
—— 商品資料
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p001’
,
‘小米電視機’
,
5000
,
‘1’
,
‘c001’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p002’
,
‘格力空調’
,
3000
,
‘1’
,
‘c001’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p003’
,
‘美的冰箱’
,
4500
,
‘1’
,
‘c001’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p004’
,
‘籃球鞋’
,
800
,
‘1’
,
‘c002’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p005’
,
‘運動褲’
,
200
,
‘1’
,
‘c002’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p006’
,
‘T恤’
,
300
,
‘1’
,
‘c002’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p007’
,
‘衝鋒衣’
,
2000
,
‘1’
,
‘c002’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p008’
,
‘神仙水’
,
800
,
‘1’
,
‘c003’
);
INSERT
INTO
products
(
pid
,
pname
,
price
,
flag
,
category_id
)
VALUES
(
‘p009’
,
‘大寶’
,
200
,
‘1’
,
‘c003’
);
然後舉例說明With as的用法:
—— 先查詢一下小米電視機和格力空調在products中的資訊
SELECT
*
FROM
products
WHERE
pname
IN
(
‘小米電視機’
,
‘格力空調’
);
查詢結果為:
那如果我們想把這個表作為一個子查詢,去查一下小米電視機和格力空調的平均價格,就可以使用with as先提取子查詢,取別名xm_gl,然後在計算:
WITH
xm_gl
AS
(
SELECT
*
FROM
products
WHERE
pname
IN
(
‘小米電視機’
,
‘格力空調’
)
)
SELECT
avg
(
price
)
FROM
xm_gl
;
結果為:
使用with as還可以建立多個臨時表,但是要注意同一個查詢語句前寫一個with就夠了,另外子查詢需要
逗號隔開
,舉個例子:
WITH
a
AS
(
SELECT
*
FROM
category
WHERE
cname
=
‘家電’
),
b
AS
(
SELECT
*
FROM
products
WHERE
pname
IN
(
‘小米電視機’
,
‘格力空調’
)
)
SELECT
*
FROM
a
LEFT
JOIN
b
ON
a
。
cid
=
b
。
category_id
;
執行結果為:
一定要逗號隔開呀,今天用的時候忘記逗號就報錯啦
上一篇:啞巴。和尚和妓女