牛客网在线编程网址:https://www.nowcoder.com/activity/oj
(默认使用SQLite)
题目1:
CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));
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`));
获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
select EMP.emp_no, MGR.emp_no manager_no, EMP.salary emp_salary, MGR.salary manager_salary FROM ( Select dept_emp.emp_no, salary, dept_no from dept_emp INNER JOIN salaries ON dept_emp.emp_no = salaries.emp_no AND dept_emp.to_date = '9999-01-01' AND salaries.to_date = '9999-01-01' ) EMP INNER JOIN ( Select dept_manager.emp_no, salary, dept_no from dept_manager INNER JOIN salaries ON dept_manager.emp_no = salaries.emp_no AND dept_manager.to_date = '9999-01-01' AND salaries.to_date = '9999-01-01' ) MGR ON EMP.dept_no = MGR.dept_no AND EMP.salary > MGR.salary;
题目2:
CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));
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);
汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
select dept_no, dept_name, title, count(*) count FROM ( Select departments.dept_no, departments.dept_name, dept_emp.emp_no, titles.title from departments INNER JOIN dept_emp ON departments.dept_no = dept_emp.dept_no AND dept_emp.to_date = '9999-01-01' INNER JOIN titles ON dept_emp.emp_no = titles.emp_no AND titles.to_date = '9999-01-01' ) temp GROUP BY dept_no, dept_name, title
题目3:
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`));
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
有同学(wasrehpic)给出了如下答案运行可通过:
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth FROM salaries AS s1, salaries AS s2 WHERE s1.emp_no = s2.emp_no AND salary_growth > 5000 AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 ) ORDER BY salary_growth DESC;
在mysql中可以用以下语句运行出结果,但在牛客网的测试用例上就有问题(另外在牛客网上YEAR函数不可用,应用上述提示的sqlite的函数)。
把年份那两句改成了一样的x-y=1的格式就可以通过了。逻辑上感觉没有差别,在本地上运行出来的结果也一样,不明白是为什么。
select s1.emp_no, s2.from_date, (s2.salary - s1.salary) salary_growth from salaries s1, salaries s2 WHERE s1.emp_no = s2.emp_no AND (YEAR(s1.from_date) = YEAR(s2.from_date) - 1 OR YEAR(s1.to_date) = YEAR(s2.to_date) - 1) AND (s2.salary - s1.salary) > 5000
order by salary_growth desc;
从答案示例来看,每年涨幅超过5000的定义通过上面两个年份的等式可以限定,不过实际定义感觉和这个还是应该不太一样,如果是说在某一年里调薪幅度超过5000,应该调薪后的'from_date'和之前某次薪水的'to_date'相差一年以内就可以计为一年内的涨幅。但其实也不知道这里的每年是什么意思,答案只是能符合测试用例的逻辑而已。