简化查询：WITH 关键字

本课属于 Coddy SQL 学习旅程的“基础知识”单元。第 49 课，共 72 课。

添加很多内部查询会让查询变得过于复杂。例如，下面这个查询包含很多子查询：

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

为了让操作更简单，我们可以使用 WITH query_name AS (...) 关键字。它可以让我们为查询命名并保存，然后在需要的地方使用：

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

这里我们在 query2 和主查询中重用了 query1。

挑战

简单

可用的表和列：
devices_specs： device_id、width、height、num_features、opinion
devices_score： device_id、score

编写一个查询，满足以下要求：

首先识别“大型设备”（width 大于 200 的设备）
然后计算这些大型设备的平均分数。将此列命名为 average_score

使用 WITH 子句解决这个问题。

亲手试试

-- 第 1 步：给只包含你关心的设备的临时结果集命名
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- 第 2 步：在主查询中重复使用这个临时结果集
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);

自我测试

这节课包含一个小测验。开始学习即可作答，并记录你的进度。

基础知识中的所有课

自己练习： SQL 在线编辑器