以下为 SQL 中高级子查询的用法解析涵盖 ANY、EXISTS 及子查询的多位置应用。学习内容ANY 关键字与子查询结果集的任一值比较等效于 IN(用于相等时)。返回多值时使用。相关子查询子查询依赖外部查询的列逐行执行。示例比较员工薪资与其部门平均值。EXISTSEXISTS逐行检查子查询结果集大时性能较优。IN先执行子查询外部表大时更快。连接需根据需求选择(如 LEFT JOIN 加 IS NULL)。判断子查询是否有记录返回适用于相关子查询。与 IN 和连接对比SELECT 中的子查询在列中嵌入子查询计算动态值。限制同一级别查询不能直接引用新列名需通过外层查询或后续语句(如 ORDER BY、HAVING)使用。FROM 中的子查询将子查询结果作为虚拟表需指定别名。可用于复杂数据处理类似临时表。示例代码与讲解1. ANY 关键字SELECT *FROM clientsWHERE client_id IN (SELECT client_idFROM invoicesGROUP BY client_idHAVING COUNT(*) 2);找出发票数 2 的客户。SELECT *FROM clientsWHERE client_id ANY (SELECT client_idFROM invoicesGROUP BY client_idHAVING COUNT(*) 2);用 ANY 替代 IN效果相同表示匹配子查询任一值。2. 相关子查询USE sql_hr;SELECT *FROM employees eWHERE salary (SELECT AVG(salary)FROM employees e2WHERE e2.office_id e.office_id);找出薪资高于所在办公室平均值的员工子查询与外部 office_id 相关。3. EXISTSUSE sql_invoicing;SELECT client_id, nameFROM clients cWHERE EXISTS (SELECT client_idFROM invoices ciWHERE c.client_id ci.client_id);找出有发票的客户逐行检查是否存在匹配记录。4. SELECT 中的子查询SELECT invoice_id, invoice_total,(SELECT AVG(invoice_total) FROM invoices) AS avginvoice_total,(SELECT avginvoice_total) - invoice_total AS differenceFROM invoices;计算每张发票与平均值的差额。注意avginvoice_total 不能直接在同级使用需通过外层查询引用。5. FROM 中的子查询SELECT *FROM (SELECT c.client_id, name,(SELECT SUM(i.invoice_total) FROM invoices i WHERE c.client_id i.client_id) AS total_sales,(SELECT AVG(invoice_total) FROM invoices) AS AVERAGE,(SELECT total_sales - AVERAGE) AS differenceFROM clients c) AS SUMMARYWHERE total_sales IS NOT NULL;将子查询结果作为虚拟表 SUMMARY筛选非空销售总额。作业1. 相关子查询USE sql_invoicing;SELECT *FROM invoices iWHERE invoice_total (SELECT AVG(invoice_total)FROM invoices i2WHERE i.client_id i2.client_id);找出总额高于其客户平均值的发票。2. EXISTSUSE sql_store;SELECT *FROM products pWHERE NOT EXISTS (SELECT *FROM order_itemsWHERE p.product_id order_items.product_id);找出从未被订购的产品。3. SELECT 中的子查询USE sql_invoicing;CLEARSELECT c.client_id, name,(SELECT SUM(i.invoice_total) FROM invoices i WHERE c.client_id i.client_id) AS total_sales,(SELECT AVG(invoice_total) FROM invoices) AS AVERAGE,(SELECT total_sales - AVERAGE) AS differenceFROM clients c;计算每个客户的销售总额、平均值及差额避免 GROUP BY。总结本次解析了 ANY(等效 IN)、相关子查询、EXISTS 及子查询在 SELECT 和 FROM 中的应用。基于 sql_invoicing、sql_hr 和 sql_store 数据库。后续将探讨视图与事务。