mysql函数变量用子查询赋值_在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子句中使用了关联标量子查询查找产品、分行和雇员的名字。