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

2021-11-05 09:29:39 浏览数 (2)

标量⼦查询

⼀般标量⼦查询,示例

查询谁的⼯资⽐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⾏记录。

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

0 人点赞