sql牛客网值得一记的题_group by 默认取非聚合的第一条记录-CSDN博客

本文链接：https://blog.csdn.net/miracleon/article/details/105515180

牛客网用的SQLite，这里的答案按照Mysql写的,有的会标注

12题

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按部门排序

select r.dept_no,r.emp_no,max(r.salary)
from (select d.dept_no,d.emp_no,s.salary from dept_emp d 
inner join salaries s on d.emp_no = s.emp_no
where d.to_date = '9999-01-01' 
and s.to_date = '9999-01-01')
as r
group by r.dept_no 
order by r.dept_no

利用了GROUP BY 默认取非聚合数据的第一条记录，所以先排好序，拿到的emp_no第一条信息，也是与最大salary匹配的

18题

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

第一种

先选出最高薪水，再剔除这一薪水的范围内求最高薪水

select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees e inner join salaries s
on  e.emp_no = s.emp_no
where s.to_date = '9999-01-01' 
and s.salary NOT IN (select max(salary) from salaries where to_date = '9999-01-01');

第二种

类似第一种

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary = (select max(salary) from salaries where salary <
                (select max(salary) from salaries where to_date = '9999-01-01')
                 and to_date = '9999-01-01');

第三种

用自连接，薪水第二高说明上面只有一个薪水，但可能有多人有相同薪水，故用DISTINCT去重
左表为s1,右表为s2,s1 有 100 100 98 96 四种薪水，则自连接之后
s1.98 < s2.100,此时s1.98关联s2.100,
对应s1.salary < s2.salary条件下HAVING COUNT(DISTINCT s2.salary) = 1
98只小于100这一个数字，所以98成立
96上面有98、100两个数字，不符合条件

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary = (select s1.salary from salaries s1 
               inner join salaries s2 
               on s1.salary < s2.salary
               where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
               group by s1.emp_no 
               having count(distinct s2.salary) = 1);

19题

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SOLUTION
连用两次left join（牛客网上只能用 INNER JOIN``LEFT JOIN）

select e.last_name,e.first_name,dp.dept_name
from  employees e 
left join 
dept_emp de on e.emp_no = de.emp_no
left join 
departments dp on dp.dept_no = de.dept_no;

21题

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

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

26题

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
注意：group by 后面的顺序不能变，先按部门再按头衔

select dp.dept_no,dp.dept_name,t.title,count(t.title) count
from departments dp,dept_emp de,titles t
where
dp.dept_no = de.dept_no
and de.emp_no = t.emp_no
and de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
group by de.dept_no,t.title

32题（拼接）

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

//SQLite
select last_name || " " || first_name as Name from employees
//MySQL
//第一种
select concat(last_name," ",first_name) as Name from employees
//第二种
select concat(concat(last_name," "),first_name) as Name from employees

35题（INSERT IGNORE INTO）

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
在这里插入图片描述

SELECT IGNORE INTO actor (actor_id,first_name,last_name,last_update)
VALUES(3,'ED','CHASE','2006-02-15 12:34:33')

38题(视图)

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

SQLite
//第一种
CREATE VIEW actor_name_view AS 
SELECT first_name AS first_name_v,last_name AS last_name_v
FROM actor
//第二种
CREATE VIEW actor_name_view (first_name_v,last_name_v) AS
SELECT first_name,last_name FROM actor

39题（强制索引）

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

//SQLite
SELECT * FROM salaries INDEXED BY idx_emp_no where emp_no = 10005
//Mysql
SELECT * FROM salaries FORCE INDEX idx_emp_no where emp_no = 10005

40题(加列)

存在actor表，包含如下列信息：

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000-00-00 00:00:00’

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL 
DEFAULT '0000-00-00 00:00:00'

COLUMN可省略

41题（触发器）

CRE构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

//SQLite
CREATE TRIGGER audit_log AFTER INERT ON employees_test
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END
//Mysql
CREATE TRIGGER audit_log AFTER INERT 
ON employees_test
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);

注：如果有多个执行语句，则命令行有变化

例：定义一个触发器，一旦有满足条件的删除操作，就会执行BEGIN和END中的语句
我们可以使用DELIMITERE指定自定义的定界符。
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$;DELIMITER $(注意：我们修改了定界符使用完了，记得修改回来DELIMITER ;)
参考代码：
在这里插入图片描述

44题（Replace）

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
replace(字符串，“需要替换的子串”，“用于替换子串的字符串”）

SQLite
//第一种
REPLACE INTO titles_test VALUES(5,10005,'1986-06-26', '9999-01-01');
//第二种
UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE
id = 5;

45题（外键）

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

//Mysql
ALTER TABLE audit ADD PRIMARY KEY (EMP_no) REFERENCES employees_test(ID)

47题（INTERSECT）

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？

select * from emp_v intersect select * from employees

49 题

针对库中的所有表生成select count(*)对应的SQL语句

//sqlite
select "select count(*) from " || name || ";" as cnts
from sqlite_master where type = 'table';
//mysql
SELECT concat("select count(*) from ",new.table_name,";") AS cnts 
FROM (SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'sqlite_master') AS new;

查询看看库里有多少个表，表名等

select * from INFORMATION_SCHEMA.TABLES

information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

51题（substr）

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

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

本题考查 substr(X,Y,Z) 或 substr(X,Y)函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。

Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

53题（group_concat）

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

//sqlite
select dept_no,group_concat(emp_no) as employees
from dept_emp group by dept_no;
//mysql
select dept_no,group_concat(emp_no separator ',') as employees
from dept_emp group by dept_no;

group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）

57题（EXISTS/NOT EXISTS）

MySQL EXISTS 和 NOT EXISTS 子查询语法如下：

SELECT … FROM table WHERE EXISTS (subquery)

SELECT … FROM table WHERE NOT EXISTS (subquery)

使用含有关键字exists查找未分配具体部门的员工的所有信息

select * from employees where not exists 
(select emp_no from dept_emp where employees.emp_no = dept_emp.emp_no)

59题（CASE）

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。当前薪水表示to_date=‘9999-01-01’
输出格式:
在这里插入图片描述

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

61题

对于employees表中，给出奇数行的first_name
在这里插入图片描述
本题描述有误，先排序，再取奇数行

select e1.first_name from employees e1 where
(select count(*) from employees e2 where e1.first_name >= e2.first_name)&1=1

奇数的确定条件，可以% 2 = 1,也可以& 1 = 1