标量⼦查询
⼀般标量⼦查询，示例
查询谁的⼯资⽐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⾏记录。
说明：上⾯的⼦查询只⽀持最多⼀列⼀⾏记录。
