牛客网sql测试（2）

21、查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL, --  '入职时间'
PRIMARY KEY (`emp_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`));

select a.emp_no, (b.salary - c.salary) as growth
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

 

本题思路是先分别用两次LEFT JOIN左连接employees与salaries，建立两张表，分别存放员工当前工资（sCurrent）与员工入职时的工资（sStart），再用INNER JOIN连接sCurrent与sStart，最后限定在同一员工下用当前工资减去入职工资。
方法一：内层用LEFT JOIN，外层用INNER JOIN（内层也可以改用 INNER JOIN）
1
2
3
4
5
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth
方法二：内外都层用FROM并列查询
1
2
3
4
5
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent,
(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart
WHERE sCurrent.emp_no = sStart.emp_no
ORDER BY growth

21、统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
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 `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`));

select de.dept_no,d.dept_name,count(salary) #注意count(s.*)不对
from departments d,salaries s,dept_emp de 
where de.emp_no=s.emp_no
and de.dept_no=d.dept_no
group by d.dept_no
本题关键是要将 每个部门分组，并分别统计工资记录总数，思路如下：
1、用INNER JOIN连接dept_emp表和salaries表，并以dept_emp.no分组，统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表，限制条件为两表的dept_no相等，找到dept_no与dept_name的对应关系，最后依次输出dept_no、dept_name、sum
1
2
3
4
SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no) 
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no

22、对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));

select s1.emp_no,s1.salary,count(distinct s2.salary) rank
from salaries s1, salaries s2
where s1.salary <= s2.salary and s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.emp_no order by rank;
本题的主要思想是复用salaries表进行比较排名，具体思路如下：
1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = '9999-01-01'，挑选出当前所有员工的薪水情况。
2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果
1
2
3
4
5
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC
最后在支持ROW_NUMBER、RANK、DENSE_RANK等函数的SQL Server数据库中，有以下参考代码，可惜在本题的SQLite数据库中不支持。
1
2
SELECT emp_no, salaries, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
WHERE to_date = '9999-01-01' ORDER BY salary DESC, emp_no ASC

23、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
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 `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_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`));

select  de.dept_no,de.emp_no,s.salary 
from dept_emp de,employees e,salaries s 
where de.emp_no=e.emp_no
and e.emp_no=s.emp_no
and s.to_date='9999-01-01'
and de.emp_no not in (select dm.emp_no from dept_manager dm)
1、先用INNER JOIN连接employees和salaries，找出当前所有员工的工资情况
2、再用INNER JOIN连接dept_emp表，找到所有员工所在的部门
3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')选出当前所有非manager员工，再依次输出dept_no、emp_no、salary
1
2
3
4
SELECT de.dept_no, s.emp_no, s.salary 
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = '9999-01-01')
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')
此外，还能作如下简化，不连接employees表也能完成：
1
2
3
SELECT de.dept_no, s.emp_no, s.salary 
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')

 

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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`));

SELECT s1.emp_no AS emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM
(SELECT s.emp_no, de.dept_no, s.salary FROM salaries s INNER JOIN dept_emp de ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01')
AS s1,
(SELECT s.emp_no, dm.dept_no, s.salary FROM salaries s INNER JOIN dept_manager dm ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01')
AS s2
WHERE s1.dept_no = s2.dept_no
AND s1.salary > s2.salary

 

本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：
1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
1
2
3
4
5
6
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

 52、获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

select first_name
from employees
order by substr(first_name,-2,2)
order by substr(first_name,-2)

substr(string,start,length)
string - 指定的要截取的字符串。
start - 必需，规定在字符串的何处开始。正数 - 在字符串的指定位置开始，负数 - 在从字符串结尾的指定位置开始，0 - 在字符串中的第一个字符处开始。
length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。

例如：select substr('abcdefg',3,4) from dual; 结果是cdef
            select substr('abcdefg',-3,4) from dual; 结果efg

注意：字符串中的第一个位置始终为1。以下两个sql查询的结果相同：

例如：select substr('abcdefg',0,3) from dual; 结果是abc

            select substr('abcdefg',1,3) from dual; 结果是abc

 

本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
1
SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1) 
1
SELECT first_name FROM employees ORDER BY substr(first_name,-2) 

 51、查找字符串'10,A,B' 中逗号','出现的次数cnt。

 

由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。详情请参考：
 
1
SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt
 
50、将employees表中的所有员工的last_name和first_name通过(')连接起来。(不支持concat，请用||实现)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)); 
select last_name||"'"||first_name as name from employees

mysql

select concat(last_name+''''+first_name) from employees

最外层的两个单引号表示字符串，从左往右数第二个是转义符，第三个是题目需要拼接的单引号

49、

针对库中的所有表生成select count(*)对应的SQL语句，如数据库里有以下表，
(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数)
employees
departments
dept_emp
dept_manage
salaries
titles

emp_bonus

那么就会输出以下的样子:
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;

select count(*) from dept_manager;
 
本题主要有以下两个关键点：
1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'，详情可参考：
2、在 SQLite 中用 “||” 符号连接字符串
1
2
SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'
 -- mysql中针对库,获取所有表名字代码为
select table_name from information_schema.tables where table_schema='shop' ;
           # （其中shop为数据库名字）
-- 合并字段用concat()
 -- 上述两步骤应用于本题为
 create table hi as 
select table_name from information_schema.tables where table_schema='shop' ;
select * from hi;
select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts from hi;
  -- 上述两个步骤合并为一条语句
select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts
 from (select table_name from information_schema.tables where table_schema='shop') as hi;
48、更新列值
请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
create table emp_bonus(
emp_no int not null,
btype smallint not null);
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`));
如：
INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); 
update salaries 
set salary = salary * 1.1
where salaries.emp_no in (select emp_no from emp_bonus)
    and to_date = '9999-01-01'
按照正常的逻辑以及之前题目的尿性，要先选出符合条件的 emp_no，即用 INNER JOIN 连接 salaries 和 emp_bonus，且用 s.to_date = '9999-01-01' 表示当前薪水，然后再用 UPDATE ... SET ... WHERE ... IN ... 语句来更新表中数据。
1
2
3
UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN
(SELECT s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb 
ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')
但又发现题目测试用例没设置好，emp_bonus里面的全部 emp_no 都是当前获奖的所有员工，于是就有了以下简易答案：
1
2
UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN 
(SELECT emp_no FROM emp_bonus)
 
谁知道还能继续投机取巧，估计是OJ系统的问题，将所有 salary 都上涨10%也能通过，于是又有了以下终极版最短答案：
1
UPDATE salaries SET salary = salary * 1.1 
 
 
 
 
 
 
 
 
 
 
 
 

posted on 2020-07-30 10:21  西兰花座  阅读(203)  评论(0)    收藏  举报

导航