牛客网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.csdn.net/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'