青盲下载,揪痧图片,杨丞琳死了吗
SELECT title , AVG(salary) AS avg FROM salaries s,titles t WHERE s.emp_no = t.emp_no AND s.to_date = '9999-01-01' AND t.to_date = '9999-01-01' GROUP BY t.title;
select emp_no, salary from salaries where to_date = '9999-01-01' order by salary desc limit 1,1;
主要思想为多层SELECT嵌套与MAX()函数结合
1、先利用MAX()函数找出salaries中当前薪水最高者
s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
3、在以上限制条件下找薪水最高者,即为所有员工薪水的次高者
SELECT em.last_name, em.first_name, dp.dept_name FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no) LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no
1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录
2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅,最后用别名growth代替
SELECT (MAX(salary)-MIN(salary)) AS growth FROM salaries WHERE emp_no = '10001' SELECT ( (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) - (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1) ) AS growth
1.找出每个员工当前工资 select e.emp_no,s.salary as sTo from employees as e left join salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01' 2.找出每个员工入职时的工资 select e.emp_no,s.salary as sHire from employees as e left join salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date 3.结合找出growth select t1.emp_no,(t1.sTo-t2.sHire) as growth from (select e.emp_no,s.salary as sTo from employees as e left join salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01') as t1 join (select e.emp_no,s.salary as sHire from employees as e left join salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date) as t2 on t1.emp_no=t2.emp_no order by growth asc;
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论