牛客网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