Sql常见面试题（总结）

### SQL常见面试题总结 #### 一、ROWNUM 使用技巧 **题目背景：** ROWNUM 是一个伪列，用于返回表中行的编号。它常用于限制查询结果的行数，例如在分页查询中。 **示例代码：** ```sql -- 示例1：查询最高薪水的前三名员工 SELECT ROWNUM, LAST_NAME, SALARY FROM ( SELECT LAST_NAME, SALARY FROM S_EMP ORDER BY SALARY DESC ) WHERE ROWNUM <= 3; -- 示例2：错误的用法，先过滤 ROWNUM 后排序 SELECT ROWNUM, LAST_NAME, SALARY FROM S_EMP WHERE ROWNUM <= 3 ORDER BY SALARY DESC; ``` **分析：** 1. **正确用法：** 先对子查询的结果进行排序，然后限制行数。 2. **错误用法：** 如果先使用 ROWNUM 过滤，再排序，则可能会导致结果不准确。因为 ROWNUM 只会在子查询中计算一次，过滤后再排序不会改变 ROWNUM 的值。 **拓展知识点：** - 如何实现更灵活的分页查询？可以考虑使用 ROW_NUMBER() 窗口函数配合分区查询。 - ROWNUM 在 Oracle 中的特殊性及其在其他数据库中的等效实现。 #### 二、ROWNUM 和 BETWEEN 子句 **题目背景：** 使用 ROWNUM 和 BETWEEN 子句来限制查询结果范围。 **示例代码：** ```sql -- 示例：尝试查询第3到第5条记录 SELECT * FROM S_EMP WHERE ROWNUM BETWEEN 3 AND 5; ``` **分析：** - 上述查询返回无记录，原因是 ROWNUM 不支持 BETWEEN 操作符。正确的做法是先使用子查询计算 ROWNUM，再限制范围。 **正确实现：** ```sql SELECT B.LAST_NAME, B.SALARY FROM ( SELECT ROWNUM AS A, B.* FROM S_EMP B ) WHERE A BETWEEN 3 AND 5; ``` **拓展知识点：** - 如何实现跨数据库的分页查询兼容性？不同数据库对分页的支持方式有所不同。 - 使用其他方法实现分页查询，如 OFFSET 和 FETCH NEXT 子句。 #### 三、查询高于部门平均薪资的员工 **题目背景：** 查询薪水高于其所在部门平均薪资的所有员工。 **示例代码：** ```sql -- 示例1：直接使用子查询 SELECT LAST_NAME, DEPT_ID, SALARY FROM S_EMP A WHERE SALARY > (SELECT AVG(SALARY) FROM S_EMP WHERE DEPT_ID = A.DEPT_ID); -- 示例2：使用连接查询 SELECT A.LAST_NAME, A.SALARY, A.DEPT_ID, B.AVG_SAL FROM S_EMP A JOIN ( SELECT DEPT_ID, AVG(SALARY) AS AVG_SAL FROM S_EMP GROUP BY DEPT_ID ) B ON A.DEPT_ID = B.DEPT_ID WHERE A.SALARY > B.AVG_SAL; ``` **分析：** 1. **子查询方式：** 直接在 WHERE 子句中使用子查询，但性能可能不佳。 2. **连接查询方式：** 使用 JOIN 来关联部门平均薪资表，提高查询效率。 **拓展知识点：** - 如何优化此类查询的性能？ - 使用窗口函数实现更高效的查询。 #### 四、查询薪水高于其直接上级的员工 **题目背景：** 查询薪水高于其直接上级（即 manager）的所有员工。 **示例代码：** ```sql SELECT ID, LAST_NAME, SALARY, MANAGER_ID FROM S_EMP A WHERE SALARY > (SELECT SALARY FROM S_EMP WHERE ID = A.MANAGER_ID); ``` **分析：** - 该查询通过子查询获取每个员工的上级薪水，并与当前员工薪水比较。 **拓展知识点：** - 如何处理复杂的层级关系查询？可以考虑使用递归查询或自连接。 - 使用其他方法提高这类查询的效率，例如建立合适的索引。 以上几个方面涵盖了 SQL 面试中常见的知识点，包括 ROWNUM 的使用、子查询与连接查询的应用以及如何高效地处理部门平均薪资和层级关系等实际问题。这些知识点不仅适用于面试准备，也对于日常工作中的数据库操作非常有帮助。