Oracle经典问题

根据提供的文件内容，我们可以归纳总结出以下几个Oracle数据库相关的知识点： ### 一、SQL查询与连接操作 #### 1. 多表左连接实现数据合并 **背景：** 假设存在两个表`A`和`B`，其中表`A`包含姓名`Name`和成绩`GRADE`，表`B`包含姓名`Name`和年龄`AGE`。 **需求：** 通过SQL语句获取以下结果： - `NAME`列包含表`B`中的所有姓名。 - `GRADE`列仅当表`A`和表`B`中的姓名相匹配时显示成绩，否则显示为`NULL`。 - `AGE`列包含表`B`中的年龄。 **解答：** ```sql CREATE TABLE A ( NAME VARCHAR(32), GRADE INT ); CREATE TABLE B ( NAME VARCHAR(32), AGE INT ); INSERT INTO A VALUES ('zhangshan', 80); INSERT INTO A VALUES ('lisi', 60); INSERT INTO A VALUES ('wangwu', 84); INSERT INTO B VALUES ('zhangshan', 26); INSERT INTO B VALUES ('lisi', 24); INSERT INTO B VALUES ('wangwu', 26); INSERT INTO B VALUES ('wutian', 26); SELECT B.NAME, A.GRADE, B.AGE FROM B LEFT OUTER JOIN A ON B.NAME = A.NAME; ``` **分析：** 这段SQL首先创建了两张表`A`和`B`并插入了一些示例数据。接着通过`LEFT OUTER JOIN`将这两张表进行连接，从而实现了数据的合并。这里的关键是`LEFT OUTER JOIN`，它确保即使没有对应的成绩记录，表`B`中的每条记录也能被包含在结果集中。 #### 2. 分组与聚合函数 **需求：** 根据名字相同的条件按年龄分组，计算每个年龄段的平均成绩。 **解答：** ```sql SELECT B.NAME, AVG(A.GRADE), B.AGE FROM B LEFT OUTER JOIN A ON B.NAME = A.NAME GROUP BY B.AGE, B.NAME; ``` **分析：** 这里使用了`LEFT OUTER JOIN`来合并表`A`和`B`，并通过`GROUP BY`子句对结果集进行分组。`AVG(A.GRADE)`用于计算每个年龄组的平均成绩。需要注意的是，在`GROUP BY`子句中同时指定了`B.AGE`和`B.NAME`，这是因为`GROUP BY`子句要求非聚合列必须出现在`GROUP BY`中，否则会报错。 ### 二、高级查询技巧 #### 1. 查找特定行数据 - **查找表中的第三行数据** ```sql SELECT LAST_NAME, SALARY FROM (SELECT S.*, ROWNUM R FROM S_EMP S) WHERE R = 3; ``` - **查找第三行到第五行之间的数据** ```sql SELECT LAST_NAME, SALARY FROM (SELECT S.*, ROWNUM R FROM S_EMP S) WHERE R BETWEEN 3 AND 5; ``` **分析：** 这里使用了`ROWNUM`伪列来实现行号功能。`ROWNUM`可以用来限制返回的行数。通过将查询结果作为子查询的一部分，并在外部查询中添加`WHERE`子句来指定所需的行范围，可以实现特定行的检索。 #### 2. 工资高于部门平均工资 - **方法一：使用子查询** ```sql SELECT LAST_NAME, DEPT_ID, SALARY FROM S_EMP WHERE SALARY > (SELECT AVG(SALARY) FROM S_EMP WHERE DEPT_ID = S_EMP.DEPT_ID); ``` - **方法二：使用连接** ```sql SELECT A.LAST_NAME, A.SALARY 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. 工资高于部门经理的工资 ```sql SELECT ID, LAST_NAME, SALARY, MANAGER_ID FROM S_EMP A WHERE SALARY > (SELECT SALARY FROM S_EMP WHERE ID = A.MANAGER_ID); ``` **分析：** 这里同样使用了子查询的方法，外层查询通过子查询获取每个员工的直接上级（即经理）的工资，并与当前员工的工资进行比较。这有助于找出工资比他们的直接上级高的员工。 #### 2. 删除重复ID_NO但保留一条记录 **需求：** 表`DEPT`中存在重复的`ID_NO`，需要删除这些重复的记录，但是必须保留至少一条记录。 **解答：** ```sql CREATE TABLE DEPT ( ID_NO INT, ID_NAME VARCHAR(20) ); INSERT INTO DEPT VALUES (1000, 's1'); INSERT INTO DEPT VALUES (1001, 's2'); INSERT INTO DEPT VALUES (1002, 's3'); INSERT INTO DEPT VALUES (1003, 's4'); INSERT INTO DEPT VALUES (1000, 's5'); INSERT INTO DEPT VALUES (1000, 's6'); INSERT INTO DEPT VALUES (1001, 's7'); -- 查询所有ID_NO重复的记录 SELECT * FROM DEPT WHERE ID_NO IN (SELECT ID_NO FROM DEPT GROUP BY ID_NO HAVING COUNT(*) > 1); ``` **分析：** 首先创建表`DEPT`并插入示例数据。接下来，使用子查询来找出所有ID_NO重复的记录。`GROUP BY`和`HAVING COUNT(*) > 1`组合使用可以找出那些出现次数多于一次的`ID_NO`。虽然题目要求删除重复记录但保留一条，但由于题目没有给出具体的删除逻辑，这里只给出了查询重复记录的SQL语句。实际操作中，可以通过其他方式（如使用`ROW_NUMBER()`函数）来确定哪些记录应该被保留或删除。 以上就是从给定的文件内容中提取出来的几个重要的Oracle数据库知识点，涵盖了基础的表操作、SQL查询以及一些高级查询技巧。