您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 子查詢實戰大全：從基礎語法到複雜嵌套與優化技巧

編輯：MySQL 資料庫相關知識

一、子查詢核心概念

子查詢是嵌套在外部查詢（Outer Query）內部的 SELECT 語句，必須置於圓括號中。支持多層嵌套，常見位置：WHERE、FROM、SELECT 列表。

二、基礎分類與語法示例

1. 標量子查詢（返回單值）

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

2. 行子查詢（返回一行多列）

SELECT * FROM t1 WHERE (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
-- 或使用 ROW 構造器
SELECT * FROM t1 WHERE ROW(col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

3. 列子查詢（返回單列多行）

配合 IN、ANY、SOME、ALL、EXISTS 使用。

4. 表子查詢（返回多行多列）

出現在 FROM 子句中，作為派生表參與連接。

三、實戰案例：商品價格分析

建表示例表 tdb_goods 並插入 20 條樣本數據（筆記本、超極本、平板、台式機等）。

1. 查詢價格高於平均價的商品

SELECT goods_id, goods_name, goods_price
FROM tdb_goods
WHERE goods_price > (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods)
ORDER BY goods_price DESC;

2. 使用 ANY / IN 查詢價格達到「超極本」任一價格的商品

SELECT goods_id, goods_name, goods_price
FROM tdb_goods
WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超極本')
ORDER BY goods_price DESC;
-- 等價寫法
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超極本')

四、進階應用：數據清洗與重構

1. 創建分類表並遷移數據（分組去重 + 寫入）

CREATE TABLE tdb_goods_cates (cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(40));
INSERT INTO tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

2. 更新主表外鍵關聯（多表 UPDATE）

UPDATE tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_name
SET g.goods_cate = c.cate_id;

3. 品牌表創建與外鍵更新（別名消除歧義）

CREATE TABLE tdb_goods_brands (brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, brand_name VARCHAR(40))
SELECT brand_name FROM tdb_goods GROUP BY brand_name;

UPDATE tdb_goods AS g
INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
SET g.brand_name = b.brand_id;

4. 修改表結構：字段重命名與類型轉換

ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

五、無限分類設計與查詢

遞歸結構表 tdb_goods_types(type_id, type_name, parent_id)：

-- 查詢所有分類及其父類
SELECT s.type_id, s.type_name, p.type_name AS parent_name
FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

-- 統計每個父類的子類數量並回寫
ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;
UPDATE tdb_goods_types AS t1
INNER JOIN (
    SELECT p.type_id, COUNT(s.type_name) AS children_count
    FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id
    GROUP BY p.type_id
) AS t2 ON t1.type_id = t2.type_id
SET t1.child_count = t2.children_count;