SQL練習16：統計出當前各個title型別對應的員工當前薪水對應的平均工資
題目鏈接：牛客網
題目描述
統計出當前(titles.to_date='9999-01-01')各個title型別對應的員工當前(salaries.to_date='9999-01-01')薪水對應的平均工資，結果給出title以及平均工資avg，
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'1986-12-01','1995-12-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
解法
根據題目的查詢要求，使用內連接的方式將兩個表進行連接，通過條件篩選后再按照title列進行分組，
SELECT title, AVG(salary)
FROM salaries s JOIN titles t
ON s.emp_no = t.emp_no AND t.to_date='9999-01-01' AND s.to_date='9999-01-01'
GROUP BY title
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/255282.html
標籤：其他
上一篇：HR常用增強
下一篇：Mysql常用命令大全