牛客网SQL错题集及知识点整理（倒序）

61. 对于employees表中，给出奇数行的first_name

SELECT first_name
FROM (
  SELECT e1.first_name, COUNT(e2.first_name) AS idx
  FROM employees e1, employees e2
  WHERE e1.first_name >= e2.first_name
  GROUP BY e1.first_name
  HAVING idx%2 =1);

未解之谜，一道我怎么看都觉得写的没问题，用SQLite online测试也没问题，但就是在牛客输出有问题的query：


等待大神解答。

---

59. 获取有奖金的员工相关信息。

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

考察CASE的用法：SQLite CASE

---

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

SELECT e.*
FROM employees e
WHERE NOT EXISTS (
    SELECT d.emp_no
    FROM dept_emp d
    WHERE e.emp_no = d.emp_no);

SQLite EXISTS

---

55. 分页查询employees表，每5行一页，返回第2页的数据

SELECT *
FROM employees
LIMIT 5,5

读题，每5行一页，返回第2页的数据，那就是要6-10的数据，也就是从5后5条数据，所以是 LIMIT 5,5。

---

54. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE salary NOT IN (
    SELECT MAX(salary)
    FROM salaries
    WHERE to_date = '9999-01-01')
AND salary NOT IN (
    SELECT MIN(salary)
    FROM salaries
    WHERE to_date = '9999-01-01')
AND to_date = '9999-01-01';

本题纯属题目描述逻辑有误，原题目为：“查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。”

应改为“查找当前(to_date = ‘9999-01-01’ )员工排除最大、最小salary之后的平均工资avg_salary。”

---

53. 按照dept_no进行汇总

SELECT dept_no, group_concat(emp_no) AS employees FROM dept_emp
GROUP BY dept_no

链接：[编程题]按照dept_no进行汇总
来源：牛客网

本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。
此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。可参考：
http://www.sqlite.org/lang_aggfunc.html#groupconcat
http://blog.youkuaiyun.com/langzxz/article/details/16807859

SQLite GROUP_CONCAT

---

52. 获取Employees中的first_name

-- 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
SELECT first_name
FROM employees
ORDER BY substr(first_name,-2);

substr(X,Y,Z)
substr(X,Y)

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

substr( string, start, length )

---

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

SELECT LENGTH('10,A,B') -LENGTH(REPLACE('10,A,B',",",""));

链接：[编程题]查找字符串 10,A,B 中逗号,出现的次数cnt
来源：牛客网

由于 SQLite
中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。详情请参考：
Sqlite数据库字符串处理函数replace
http://sqlite.org/lang_corefunc.html#replace

replace(X,Y,Z)

The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.

---

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

SELECT "select count(*) from "|| name ||";" AS cnts
FROM sqlite_master 
WHERE type ='table'

SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。

sqlite的系统表sqlite_master

---

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

牛客系统能通过的query：

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID)
);

其实是对的，但牛客系统不完整所以不通过的query：

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no         INT       NOT NULL      REFERENCES employees_test(ID),
    create_date    datetime  NOT NULL
);

---

47. 如何获取emp_v和employees有相同的数据no

SELECT *
FROM emp_v
INTERSECT
SELECT *
FROM employees;

SQLite INTERSECT operator compares the result sets of two queries and returns distinct rows that are output by both queries.
SQLite INTERSECT

--The following illustrates the syntax of the INTERSECT operator:
SELECT select_list1
FROM table1
INTERSECT
SELECT select_list2
FROM table2

SQL 操作结果集 -并集、差集、交集、结果集排序

---

45. 将titles_test表名修改为titles_2017

ALTER TABLE titles_test 
RENAME TO titles_2017;

---

44. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

UPDATE titles_test 
SET emp_no = REPLACE(emp_no,10001,10005) 
WHERE id = 5;

REPLACE(字段，“需要替换的值”，“替换后的值”）

---

43. 将所有to_date为9999-01-01的全部更新为NULL

UPDATE titles_test
SET to_date = NULL,
    from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

首先是记住语句逻辑，还有就是多列更新时用逗号连接，而不是AND。

---

42. 删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test 
WHERE id NOT IN (
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no);

DELETE FROM table_name
WHERE filter_condition_1
AND …;

---

41. 构造一个触发器audit_log

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

只能说记住它。需要注意的是

BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
SQLite 触发器（Trigger）

---

40. 在last_update后面新增加一列名字为create_date

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

---

39. 针对上面的salaries表emp_no字段创建索引idx_emp_no

SELECT * 
FROM salaries 
INDEXED BY idx_emp_no 
WHERE emp_no = 10005；

强制索引查询

---

38. 针对actor表创建视图actor_name_view

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor;

CREATE VIEW view_name AS
SELECT …
FROM …
… ;

---

37. 对first_name创建唯一索引uniq_idx_firstname

CREATE UNIQUE INDEX uniq_idx_firstname
ON actor(first_name);

CREATE INDEX idx_lastname
ON actor(last_name);

CREATE INDEX 命令

CREATE INDEX 的基本语法如下：
CREATE INDEX index_name ON table_name;

单列索引

单列索引是一个只基于表的一个列上创建的索引。基本语法如下：
CREATE INDEX index_name ON table_name (column_name);

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_name on table_name (column_name);

组合索引

组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下：

CREATE INDEX index_name on table_name (column1, column2);

SQLite 索引

---

36. 创建一个actor_name表

CREATE TABLE actor_name AS 
SELECT first_name, last_name
FROM actor;

详见《SQL必知必会》读书笔记》第15课插入数据，第二部分“从一个表复制到另一个表”。

---

35. 批量插入数据，不使用replace操作

-- 对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
INSERT OR IGNORE INTO actor (
    actor_id, first_name, last_name, last_update)
VALUES ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

这里是因为题中写到“如果数据已经存在，请忽略”，所以在INSERT后加上OR IGNORE，其余部分与正常插入数据相同。

---

34. 批量插入数据

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
       (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

批量插入格式：
INSERT INTO table_name (column_1, column_2, … , column_n)
VALUES (…, …, …, …), (…, …, …, …), (…, …, …, …);

---

33. 创建一个actor表，包含如下列信息

CREATE TABLE 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'));
);

这里要注意的是建表格式和PRIMARY KEY及DEFAULT的设置。

---

31. 获取select * from employees对应的执行计划

EXPLAIN
SELECT *
FROM employees;

学习的时候是没有了解过关于“执行计划”的部分的，Google之后觉得这篇写的很好，分享给大家：写SQL要学会使用"执行计划"。

总的来说，这里就是在语句前加上一个EXPLAIN。

---

28. 查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

-- 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)
-- 以及电影数目(count(film.film_id))，
-- 而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
SELECT DISTINCT c.name, COUNT(f.film_id)
FROM film f
INNER JOIN film_category fc
ON f.film_id = fc.film_id
AND f.description LIKE '%robot%'
INNER JOIN category c
ON fc.category_id = c.category_id
WHERE fc.category_id IN (
    SELECT category_id
    FROM film_category
    GROUP BY category_id
    HAVING COUNT(DISTINCT film_id) >= 5);

首先还是审题，这里说的是该分类包含电影总数量(count(film_category.category_id))>=5部，而不是描述信息中包含robot的电影对应的分类包含电影总数量>=5。所以在筛选category的时候要用一个子查询而不是直接筛选。

---

22. 统计各个部门的工资记录数

SELECT d.dept_no, d.dept_name, COUNT(s.salary)
FROM departments d
INNER JOIN dept_emp de
ON d.dept_no = de.dept_no
INNER JOIN salaries s
ON de.emp_no = s.emp_no
GROUP BY d.dept_no, d.dept_name;

三表联结，GROUP BY后可以有多个筛选条件，依旧要注意SELECT语句中只能有GROUP BY 后的筛选条件和聚合函数。

---

21. 查找所有员工自入职以来的薪水涨幅情况

-- 分别建立现在薪水和入职薪水的表，然后二表联结
SELECT t1.emp_no, (t1.now_s - t2.hire_s) AS growth
FROM (
    SELECT emp_no, salary AS now_s
    FROM salaries
    WHERE to_date = '9999-01-01') AS t1
INNER JOIN (
    SELECT s.emp_no, s.salary AS hire_s
    FROM salaries s
    INNER JOIN employees e
    ON s.emp_no = e.emp_no
    AND s.from_date = e.hire_date) AS t2
ON t1.emp_no = t2.emp_no
ORDER BY growth;


-- employee表和两张salaries表联结
SELECT e.emp_no, (s1.salary - s2.salary) AS growth
FROM employees e
INNER JOIN salaries s1
ON e.emp_no = s1.emp_no
AND s1.to_date = '9999-01-01'
INNER JOIN salaries s2
ON e.emp_no = s2.emp_no
AND e.hire_date = s2.from_date
ORDER BY growth

---

19. 查找所有员工的last_name和first_name以及对应的dept_name

SELECT e.last_name, e.first_name, d.dept_name
FROM employees e
LEFT JOIN dept_emp de
ON e.emp_no = de.emp_no
LEFT JOIN departments d
ON de.dept_no = d.dept_no;

白痴题，三表联合，注意要所有员工，包括未分department的员工，所以用 LEFT JOIN。

---

17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary = (
    SELECT salary
    FROM salaries
    WHERE to_date = '9999-01-01'
    GROUP BY salary
    ORDER BY salary DESC
    LIMIT 1,1);

个人觉得非常严谨的一个写法，思路是先用salary分组，这样的话避免了多个员工同样薪水，然后用LIMIT(1,1)直接抽取出第二多的薪水值，再从原表对应这个薪水值的emp_no。

还有一种方法是把GROUP BY实现的功能用DISTINCT来代替，即

SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary = (
    SELECT DISTINCT salary
    FROM salaries
    WHERE to_date = '9999-01-01'
    ORDER BY salary DESC
    LIMIT 1,1);

---

12. 获取所有部门中当前员工薪水最高的相关信息

SELECT d1.dept_no, s1.emp_no, s1.salary
FROM salaries s1
INNER JOIN dept_emp d1
ON s1.emp_no = d1.emp_no
AND s1.to_date = '9999-01-01'
AND d1.to_date = '9999-01-01'
INNER JOIN (
    SELECT d2.dept_no, MAX(s2.salary) as max_s
    FROM salaries s2
    INNER JOIN dept_emp d2
    ON s2.emp_no = d2.emp_no
    AND s2.to_date = '9999-01-01'
    AND d2.to_date = '9999-01-01'
    GROUP BY d2.dept_no) t
ON s1.salary = t.max_s
AND d1.dept_no = t.dept_no
ORDER BY d1.dept_no;

首先是读题，

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

所有部门 - GROUP BY dept_no

思路：按照过滤条件内联结两表，然后内联结筛选出的每部门编号及对应最大薪水值，以确定emp_no

这里需要注意的时GROUP BY的用法，SELECT语句中只能包含聚集函数和GROUP BY后的筛选列。

---

10. 获取所有非manager的员工emp_no

SELECT DISTINCT e.emp_no
FROM employees e
WHERE e.emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager);

白痴题+1，考察 NOT IN 语句， 为防止重复记得加 DISTINCT。

---

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary

SELECT DISTINCT dm.dept_no, dm.emp_no, s.salary
FROM dept_manager dm
INNER JOIN salaries s
ON dm.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
AND dm.emp_no = s.emp_no;

又一道不知道怎么会出错的题目，考察内联结，需要注意两表都需要对to_date作出限制，另外SELECT语句中记得加表别名限制就好。

• DISTINCT 在SELECT语句中必须放在最前面
• DISTINCT 作用于SELECT语句中的所有列

---

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no

SELECT e.last_name, e.first_name, d.dept_no
FROM employees e
LEFT OUTER JOIN dept_emp d
ON e.emp_no = d.emp_no;

没啥说的，考察外联结，也不知道当时怎么会不一次通过，看了题解区大家的讨论，可能是因为这里的DBMS只接受 LEFT OUTER JOIN, 不接受 FULL OUTER JOIN 和 RIGHT OUTER JOIN。

所以说大家还是多用 INNER JOIN 和 LEFT OUTER JOIN 吧，如《SQL必知必会》所说，确实这个关键字的可移植性比较高。

---

2. 查找入职员工时间排名倒数第三的员工所有信息

-- 法一：直接找到仅小于两个unique日期的员工emp_no
SELECT *
FROM employees
WHERE emp_no IN(
    SELECT e1.emp_no
    FROM employees e1,  employees e2
    WHERE e1.hire_date < e2.hire_date
    GROUP BY e1.emp_no
    HAVING COUNT(DISTINCT e2.hire_date)=2 
);

-- 法二：先确定排倒数第二的hire_date
SELECT * 
FROM employees 
WHERE hire_date = (
    SELECT DISTINCT hire_date
    FROM employees
    ORDER BY hire_date DESC 
    LIMIT 2,1
);

法一把问题转化为了常见的排序问题。要注意的是：

• 在SELECT指定的字段要么就要包含在GROUP BY语句的后面，作为分组的依据；要么就要被包含在聚合函数中。所以不能直接SELECT e1.*（即使在牛客系统中是可以通过的）
• 这里用IN，不用=，是为了避免多个emp_no的出现
• 用DISTINCT可避免多个员工同日入职

法二类同法一，但使用LIMIT可精简SQL语句。