[大数据]MySQL(24)——where后面的标量子查询使用

?标量子查询

（1）谁的工资比Abel高

SELECT 
	*
FROM
	`employees`
WHERE
	salary > (	
		SELECT 
			salary
		FROM
			`employees`
		WHERE 
			last_name = 'Abel'
);

（2）返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资

SELECT
	last_name,
	job_id,
	salary
FROM
	`employees`
WHERE
	`job_id` = (
		SELECT
			job_id
		FROM
			`employees`
		WHERE
			`employee_id` = 141
) AND salary > (
		SELECT
			salary
		FROM
			`employees`
		WHERE
			`employee_id` = 143
);

（3）返回公司工资最少的员工的last_name,job_id和salary

SELECT 
	last_name,
	job_id,
	salary
FROM
	`employees`
WHERE
	salary = (
		SELECT 	
			MIN(salary)
		FROM
			`employees`
);

（4）查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT 
	MIN(salary),
	`department_id`
FROM
	`employees`
GROUP BY
	`department_id`
HAVING MIN(salary) > (
		SELECT 
			MIN(salary)
		FROM
			`employees`
		WHERE
			`department_id` = 50
);