在MySQL中使用子查询

子查询作为数据源

子查询生成的结果集包含行、列数据，因而非常适合将它与表一起包含在from子句的子查询里。例：

SELECT d.dept_id, d.name, e_cnt.how_many num_employees
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 'zifeiy' name, 0 low_limit, 4999.99 high_limit
UNION ALL
SELECT 'feiyzi' name, 5000 low_limit, 9999.99 high_limit
UNION ALL 
SELECT 'hahahe' name, 10000 low_limit, 9999999.99 high_limit;

结果：

+--------+-----------+------------+
| name   | low_limit | high_limit |
+--------+-----------+------------+
| zifeiy |         0 |    4999.99 |
| feiyzi |      5000 |    9999.99 |
| hahahe |     10000 | 9999999.99 |
+--------+-----------+------------+
3 rows in set (0.00 sec)

上面的SQL能够生成一个包含3组数据的结果集，然后我们将其滋味子查询添加到下面的SQL中：

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 
(SELECT 'zifeiy' name, 0 low_limit, 4999.99 high_limit
UNION ALL
SELECT 'feiyzi' name, 5000 low_limit, 9999.99 high_limit
UNION ALL 
SELECT 'hahahe' 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;

过滤条件中的子查询

过滤条件中的子查询不一定出现在where子句中，下面的例子演示在having子句中使用子查询来查找开户最多的雇员：

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
);

子查询作为表达式生成器

单行单列的标量子查询，除了用于过滤条件中外，1还可以用在表达式可以出现的任何位置。其中包括查询中的select和order by子句以及insert语句中的values子句。例：

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) 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
ORDER BY 1,2;

此SQL在select子句中使用了子查询，它在select子句中使用了关联标量子查询查找产品、分行和雇员的名字。