查询工资大于149号员工工资的人数
- 从里往外写
2.从外往里写
SELECT employee_id,last_name,salary FROM employees WHERE slary > (SELECT salary FROM employees WHERE employee_id==149)
返回job_id 与 141号员工相同,salary比143号员工多的 姓名 job_id 和工资
1.先查下job_id == 141 SELECT job_id FROM employess WHERE employee_id = 141
2.再查143号员工的工资
SELECT salary FROM employees WHERE employee_id = 143
3.查下符合条件的数据返回job_id ,employee_id,salary
SELECT job_id ,employee_id,salary FROM employee WHERE
( SELECT job_id FROM employess WHERE employee_id = 141)
AND (SELECT salary FROM employess WHERE employee_id = 143)
返回公司里面工资最低的员工的last_name,job_id和salary
- 先查出最小工资 SELECT MIN(salary) FROM employees
2.再根据数值去匹配其相关信息
SELECT last_name,job_id,salary FROM employees WHERE salary = SELECT MIN(salary) FROM employees
查询与141或者174号员工的manager_id和department_id 相同的其他员工的employee_id,manager_id,department_id;
方式1
- 先查询141的manager_id和department_id SELECT manager_id,department_id FROM employess WHERE employee_id = 141
- 先查询141的manager_id和department_id SELECT manager_id,department_id FROM employess WHERE employee_id = 174
3.匹配前面两个条件 筛选出数据 SELECT employee_id,manager_id,department_id WHERE (department_id SELECT manager_id,department_id FROM employess WHERE employee_id = 141) OR (SELECT manager_id,department_id FROM employess WHERE employee_id = 174)
4.使用in优化查询 SELECT employee_id,manager_id,department_id FROM employees WHERE manager_id=(SELECT manager_id FROM employess WHERE employee_id IN(141,174) AND department_id = (SELECT manager_id FROM employees WHERE employee_id IN(414,174)) AND employee_id <>141
方式2 and 成对匹配
示例: SELECT employee_id,manager_id,department_id FROM employees WHERE(manager_id,department_id) = (SELECT manager_id,department_id FROM employees WHERE employee_id = 141)
