MySQL 中使用子查询总结

---

1. 子查询的分类

1.1. 非关联子查询

子查询语句可以单独执行，不需要引用包含语句中的任何内容。称为非关联子查询。

1.1.1. 单行单列子查询

子查询语句返回一个单行单列的表，这种类型的子查询称为标量子查询，并且可以位于常用运算符（=、<>、<、>、<=、>=）的任意一边。

SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE account_id = (
	SELECT MAX(account_id) FROM account
);

SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE open_emp_id <> (
	SELECT e.emp_id FROM employee e 
	INNER JOIN branch b ON e.assigned_branch_id = b.branch_id
	WHERE e.title = 'Head Teller' AND b.city = 'Woburn'
);

1.1.2. 多行单列子查询

子查询语句返回一个多行单列的表。可以使用的运算符：IN、NOT IN、ALL、ANY。

IN运算符用于查看是否能在一个表达式集合中找到某一个表达式。

# IN 运算符
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id IN (
	SELECT superior_emp_id FROM employee
);

NOT IN运算符用于查看是否某一个表达式不在一个表达式集合中。

# NOT IN 运算符
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id NOT IN (
	SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL
);

ALL运算符用于将某单值与集合中的每个值进行比较。构建这种条件需要将一个比较运算符（=、<>、<、>、<=、>=）与ALL运算符配合使用。只有与集合中所有成员比较都成立时条件才为真。

# 查找雇员ID与任何主管ID不同的所有雇员。这里和上面使用 NOT IN 的结果是一样的。
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id <> ALL (
	SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL
);

NOT IN与<> ALL运算符等效。一般直接使用NOT IN即可。
当使用NOT IN或者<> ALL运算符比较一个值与一个集合时，需要确保值集合中不包含NULL值，这是因为任何一个将值与NULL进行比较的行为都将产生未知的结果。

# 查找可用余额小于（Frank Tucker 所有账户 中最小余额的账户 的余额）的账户。
SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE avail_balance < ALL(
	SELECT a.avail_balance FROM account a
	INNER JOIN individual i ON a.cust_id = i.cust_id
	WHERE i.fname = 'Frank' AND i.lname = 'Tucker'
);

ANY运算符允许将一个值与一个集合中的每个值进行比较，只要有一个成立，则条件为真。

# 查找可用余额大于（Frank Tucker 所有账户 中任意账户 的余额）的账户。
SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE avail_balance > ANY(
	SELECT a.avail_balance FROM account a
	INNER JOIN individual i ON a.cust_id = i.cust_id
	WHERE i.fname = 'Frank' AND i.lname = 'Tucker'
);

IN与= ANY运算符等效。一般直接使用IN即可。

1.1.3. 多列子查询

子查询语句返回一个多列的表。

SELECT account_id, product_cd, cust_id FROM account WHERE(open_branch_id, open_emp_id) IN (
	SELECT b.branch_id, e.emp_id FROM branch b 
	INNER JOIN employee e ON b.branch_id = e.assigned_branch_id
	WHERE b.name = 'Woburn Branch' AND (e.title = 'Teller' OR e.title = 'Head Teller')
);

上面语句等价的单列子查询：

SELECT account_id, product_cd, cust_id FROM account WHERE open_branch_id = (
	SELECT branch_id FROM branch WHERE name = 'Woburn Branch' AND open_emp_id IN (
		SELECT emp_id FROM employee WHERE title = 'Teller' OR title = 'Head Teller'
	)
);

1.2. 关联子查询

关联子查询依赖包含语句并引用其一列或者多列。与非关联子查询不同，关联子查询不是在包含语句执行之前一次执行完毕，而是为每一个候选行执行一次。

#计算每个客户的账户数，检索出那些拥有两个账户的客户
#先从 customer 表中检索出客户记录，接着为每个客户执行一次子查询，每次执行都要向子查询传递 c.cust_id，若子查询返回2，则满足条件，该行将被加入到结果集。
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c WHERE 2 = (
	SELECT COUNT(*) FROM account a WHERE a.cust_id = c.cust_id
);
#检索所有账户总额在5000到10000之间的所有客户。
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c WHERE (
	SELECT SUM(a.avail_balance) FROM account a WHERE a.cust_id = c.cust_id
) BETWEEN 5000 AND 10000;

EXISTS和NOT EXISTS运算符，会检查子查询能否返回至少一行，所以包含查询的条件只需要知道子查询返回的结果是多少行，而与结果的具体内容无关，所以这里可以直接使用SELECT 1。

#EXISTS ，查询2000-1-15日有交易记录的账户
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a WHERE EXISTS (
	SELECT 1 FROM `transaction` t WHERE t.account_id = a.account_id AND t.txn_date = '2000-1-15'
);

#NOT EXISTS ，查询所有非商业客户
SELECT a.account_id, a.product_cd, a.cust_id FROM account a WHERE NOT EXISTS (
	SELECT 1 FROM business b WHERE b.cust_id = a.cust_id
);

更新数据，将最新的交易时间更新到 account 表中。

UPDATE account a SET a.last_activity_date = (
	SELECT MAX(t.txn_date) FROM `transaction` t WHERE t.account_id = a.account_id
)WHERE EXISTS (
	SELECT 1 FROM `transaction` t WHERE t.account_id = a.account_id
);

删除数据，将没有职员的部门删掉。

DELETE FROM department WHERE NOT EXISTS (
	SELECT 1 FROM employee WHERE employee.dept_id = department.dept_id
);

注意，在MySQL中DELETE语句使用关联子查询时，不能使用表别名，会报语法错误。

mysql> DELETE FROM department d WHERE NOT EXISTS (
	SELECT 1 FROM employee e WHERE e.dept_id = d.dept_id
);
#提示如下错误
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd WHERE NOT EXISTS (
	SELECT 1 FROM employee e WHERE e.dept_id = d.dept_id
)' at line 1
mysql> 

2. 子查询的使用

2.1. 作为数据源

子查询在FROM子句中使用必须是非关联的，它们首先执行，然后一直保留在内存中直至包含查询执行完毕。

#查询每个部门的雇员人数
SELECT d.dept_id, d.`NAME`, e_cnt.how_many FROM department d 
INNER JOIN (
	SELECT dept_id, COUNT(*) how_many FROM employee GROUP BY dept_id
) e_cnt ON d.dept_id = e_cnt.dept_id;

除了使用查询总结现有数据，使用子查询还可以生成数据库中不存在的数据。

#按照账户余额对客户进行分组，但是这些组的定义根本没有存储在数据库中。需要用子查询进行生成。
SELECT `groups`.name, COUNT(*) num_customers FROM (
	#使用子查询生成临时表
	SELECT SUM(a.avail_balance) cust_balance FROM account a 
	INNER JOIN product p ON a.product_cd = p.product_cd 
	WHERE p.product_type_cd = 'ACCOUNT' GROUP BY a.cust_id
) cust_rollup 
INNER JOIN (
	#使用子查询生成临时表，这里注意groups一定要加撇号，否则会报语法错误（因为groups是8.0版本新增的保留字）
	SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit
	UNION ALL
	SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
	UNION ALL
	SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit
) `groups` ON cust_rollup.cust_balance BETWEEN `groups`.low_limit AND `groups`.high_limit
GROUP BY `groups`.name;

当然，我们可以直接新建一张表来记录这些原本不存在的数据，但是这样时间长了就会发现数据库因为这些特殊目的而建的表变得很多，并且杂乱，难以维护。所以还是对于这种类似需求，还是直接使用子查询的好。坚持这样一个原则：仅当有明确的商业需求保存这些新数据时才能添加相应的新表到数据库。

#根据账户类型，开户雇员，及开户行分组。对所有储蓄账户余额求和
SELECT p.`name` product, b.`name` branch, CONCAT(e.fname,' ',e.lname) name, account_groups.tot_deposits FROM (
	SELECT product_cd, open_branch_id, open_emp_id, SUM(avail_balance) tot_deposits 
	FROM account GROUP BY product_cd, open_branch_id, open_emp_id
) account_groups
INNER JOIN employee e ON e.emp_id = account_groups.open_emp_id
INNER JOIN branch b ON b.branch_id = account_groups.open_branch_id
INNER JOIN product p ON p.product_cd = account_groups.product_cd
WHERE p.product_type_cd = 'ACCOUNT' ORDER BY 1,2;

#等价的SQL语句，这里增加了排序
SELECT p.`name` product, b.`name` branch, CONCAT(e.fname,' ',e.lname) name, SUM(avail_balance) tot_deposits 
FROM account a 
INNER JOIN employee e ON e.emp_id = a.open_emp_id
INNER JOIN branch b ON b.branch_id = a.open_branch_id
INNER JOIN product p ON p.product_cd = a.product_cd
WHERE p.product_type_cd = 'ACCOUNT'
GROUP BY p.`name`, b.`name`, e.fname, e.lname ORDER BY 1,2;

2.2. 作为过滤条件

#查询开户最多的雇员
SELECT open_emp_id, COUNT(*) how_many FROM account 
GROUP BY open_emp_id HAVING COUNT(*) = (
	SELECT MAX(emp_cnt.how_many) FROM (
		SELECT COUNT(*) how_many FROM account GROUP BY open_emp_id
	) emp_cnt
);

2.3. 作为表达式生成器

单行单列子查询，也就是标量子查询，除了可以用于过滤条件中，还能用在表达式可以出现的任何位置，包括SELECT子句和ORDER BY子句，以及INSERT语句中的VALUES子句。

在SELECT子句中使用子查询。

#根据账户类型，开户雇员，及开户行分组。对所有储蓄账户余额求和
SELECT all_prods.product, all_prods.branch, all_prods.name, all_prods.tot_deposits
FROM (
	SELECT (
		SELECT p.name FROM product p WHERE p.product_cd = a.product_cd AND  p.product_type_cd = 'ACCOUNT'
	) product,
	(
		SELECT b.name FROM branch b WHERE b.branch_id = a.open_branch_id
	) branch,
	(
		SELECT CONCAT(e.fname,' ',e.lname) name FROM employee e WHERE e.emp_id = a.open_emp_id
	) name,
	SUM(a.avail_balance) tot_deposits
	FROM account a
	GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id
) all_prods
WHERE all_prods.product IS NOT NULL
ORDER BY 1,2;

在ORDER BY子句中使用子查询。

#查询雇员数据，根据老板的姓和雇员的姓排序。
SELECT emp.emp_id, CONCAT(emp.fname, ' ', emp.lname) emp_name ,(
	SELECT CONCAT(boss.fname, ' ', boss.lname) FROM employee boss WHERE boss.emp_id = emp.superior_emp_id
) boss_name
FROM employee emp WHERE emp.superior_emp_id IS NOT NULL
ORDER BY (
	SELECT boss.lname FROM employee boss WHERE boss.emp_id = emp.superior_emp_id
), emp.lname;

除了上面的关联标量子查询，还可以使用非关联标量子查询为INSERT子句生成值。

#生成一个新的账户
INSERT INTO `account` (`account_id`, `product_cd`, `cust_id`, `open_date`, `last_activity_date`, `status`, `open_branch_id`, `open_emp_id`, `avail_balance`, `pending_balance`) 
VALUES (
	NULL, 
	(SELECT product_cd FROM product WHERE name = 'savings account'), 
	(SELECT cust_id FROM customer WHERE fed_id = '555-55-5555'), 
	'2000-01-15',
	'2005-01-04',
	'ACTIVE', 
	(SELECT branch_id FROM branch WHERE name = 'Quincy Branch'), 
	(SELECT emp_id FROM employee WHERE lname = 'Portman' AND fname = 'Frank'), 
	0, 
	0
);

这种方法有一个缺点，当插入的列允许为NULL时，即使子查询不能反回值，INSERT语句也会成功，此时该字段会被置为NULL，可能偏离了我们的预期。
将查询结果插入到新表中的另一种形式：
（1）如果两张表（导出表和目标表）的字段类型和顺序都一致：

INSERT INTO target_table_name SELECT * FROM source_table_name;

示例：

INSERT INTO `account` SELECT NULL, 'SAV', 5, '2000-01-15', NULL, '2005-01-04','ACTIVE', 3, 15, 0, 0 FROM DUAL;

（2）如果只希望导入部分指定字段，需要指定目标表的字段名，并且导出表需要按照相同字段类型顺序排列查询字段：

INSERT INTO target_table_name (column1, column2, ...) SELECT column1x, column2x, ... FROM source_table_name;

示例：

INSERT INTO `account` ( `product_cd`, `cust_id`, `open_date`, `last_activity_date`, `status`, `open_branch_id`, `open_emp_id`, `avail_balance`, `pending_balance`) 
SELECT 'SAV', 5, '2000-01-15', '2005-01-04','ACTIVE', 3, 15, 0, 0 FROM DUAL;

SQL 将查询结果插入到另一张表中

补充示例：

INSERT INTO data_site_new(id, source_type, site_url, site_name, site_category, media_name, media_type, media_level, media_country, media_language_code, create_time, update_time)
SELECT NULL, source_type, site_url, site_name, site_category_code, media_name, NULL, media_level, country_code, media_language_code, NOW(), NOW() 
FROM data_site WHERE site_url  IS NOT NULL AND source_type IS NOT NULL AND site_url LIKE 'http%' AND source_type NOT LIKE '%OldDC%';

INSERT INTO data_govern.govern_rule
(name, source_type, create_time, update_time)
SELECT name, source_type, NOW(), NOW() FROM data_center.source_supplier;

3. 子查询的总结

• 子查询返回的结果可以是单行单列，多行单列，多行多列。
• 子查询可以独立于包含语句（非关联子查询）。
• 子查询可以引用包含语句中的一行或者多行（关联子查询）。
• 子查询可以用于条件中，这些条件使用比较运算符以及其他特殊目的的运算符（in、not in、exists、not exists）。
• 子查询可以出现在select、update、delete、insert语句中。
• 子查询产生的结果集可以与其他表或者其他子查询进行连接。
• 子查询可以生成值来填充表或者查询结果集中的一些列。
• 子查询可以用于查询中的select、from、where、having、order by子句。