MySQL数据库，子查询学习，高手必备(二)

标量⼦查询

⼀般标量⼦查询，示例

查询谁的⼯资⽐Abel的⾼？

/*①查询abel的⼯资【改查询是标量⼦查询】*/

SELECT salary

FROM employees

WHERE last_name = 'Abel';

/*②查询员⼯信息，满⾜salary>①的结果*/

SELECT *

FROM employees a

WHERE a.salary > (SELECT salary

FROM employees

WHERE last_name = 'Abel');

多个标量⼦查询，示例

返回jobid与141号员⼯相同，salary⽐143号员⼯多的员⼯、姓名、jobid和⼯资

/*返回job_id与141号员⼯相同，salary⽐143号员⼯多的员⼯、姓名、job_id和⼯资*/

/*①查询141号员⼯的job_id*/

SELECT job_id

FROM employees

WHERE employee_id = 141;

/*②查询143好员⼯的salary*/

SELECT salary

FROM employees

WHERE employee_id = 143;

/*③查询员⼯的姓名、job_id、⼯资，要求job_id=① and salary>②*/

SELECT

a.last_name 姓名,

a.job_id, a.salary ⼯资

FROM employees a

WHERE a.job_id = (SELECT job_id

FROM employees

WHERE employee_id = 141)

AND

a.salary > (SELECT salary

FROM employees

WHERE employee_id = 143);

⼦查询+分组函数，示例

查询最低⼯资⼤于50号部门最低⼯资的部门id和其最低⼯资【having】

/*查询最低⼯资⼤于50号部门最低⼯资的部门id和其最低⼯资【having】*/

/*①查询50号部门的最低⼯资*/

SELECT min(salary)

FROM employees

WHERE department_id = 50;

/*②查询每个部门的最低⼯资*/

SELECT

min(salary),

department_id

FROM employees

GROUP BY department_id;

/*③在②的基础上筛选，满⾜min(salary)>①*/

SELECT

min(a.salary) minsalary,

department_id

FROM employees a

GROUP BY a.department_id

HAVING min(a.salary) > (SELECT min(salary)

FROM employees

WHERE department_id = 50);

错误的标量⼦查询，示例

将上⾯的⽰例③中⼦查询语句中的min(salary)改为salary，执⾏效果如下：

mysql> SELECT

min(a.salary) minsalary,

department_id FROM employees a

GROUP BY a.department_id

HAVING min(a.salary) > (SELECT salary

FROM employees

WHERE department_id = 500000);

ERROR 1242 (21000): Subquery returns more than 1 row

错误提⽰：⼦查询返回的结果超过了1⾏记录。

说明：上⾯的⼦查询只⽀持最多⼀列⼀⾏记录。