#2、列子查询(多行)
#案例1:返回location_id是1400或1700的部门中的所有员工姓名
#(1)查询所有员工姓名
SELECT `last_name`
FROM `employees`;
#(2)查询location_id是1400或1700的部门中的所有员工部门编号
SELECT `department_id`
FROM `departments`
WHERE `location_id`IN(1400,1700);
#综合(1)和(2)
SELECT `last_name`
FROM `employees`
WHERE `department_id`
IN(
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id`IN(1400,1700)
);
#案例2:返回其他部门中比job_id为‘IT_PROG’部门任一工资低
#的员工的:工号、姓名、job_id以及salary
#(1)查询job_id为‘IT_PROG’部门的工资
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG';
#(2)查询其他工种中比job_id为‘IT_PROG’部门任一工资低的员工的:工号、姓名、job_id以及salary
SELECT `job_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary` < ANY(
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG'
)AND `job_id` <> 'IT_PROG';
#案例3:f返回其他部门中比job_id为‘IT_PROG’部门所有员工工资都低的员工的
#员工号、姓名、job_id以及salary
#(1)查询job_id为‘IT_PROG’部门的员工的最低工资
SELECT salary
FROM `employees`
WHERE `job_id`='IT_PROG';
#(2)查询员工号、姓名、job_id以及salary满足(1)
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`<ALL(
SELECT salary
FROM `employees`
WHERE `job_id`='IT_PROG'
);
列子查询
最新推荐文章于 2024-08-03 23:45:10 发布