曾经在面试过程中遇到的关于Oracle的查询

以前在国企中做开发使用的是Oracle数据库，因此对这个数据库也进行了全面学习，经常要写增删改查，试图，存储过程等语句。这是之前换工作的时候在面试过程中遇到的几个关于Oracle数据库方面的试题，个人感觉还是很不错的，我们在项目中也会遇到类似的需求。

创建表

首先我们要在数据库中创建三张表（用户表，角色表，用户角色表），脚本如下：

/*创建用户表*/
create table USERS
 (
   USERID   NUMBER(4) not null,
   USERNAME NVARCHAR2(30)
 )

/*创建角色表*/
create table ROLE
(
   ROLEID   NUMBER(2) not null,
   ROLENAME NVARCHAR2(30)
 )
 
/*创建用户角色关系表*/
 create table USERROLES
 (
   USERID NUMBER(4),
   ROLEID NUMBER(2)
 )

问题一

有三张表用户表（users），角色表（role），用户角色表（userroles），用户与角色的关系是多对多，要求查询显示的结果如下：　

下面通过两种方式实现

/* 方法一，使用分组函数 group by */
SELECT W.USERNAME, MAX(DECODE(T.ROLEID, 1, '管理员', NULL)) || ',' || MAX(DECODE(T.ROLEID, 2, '普通用户', NULL)) AS "角色"
  FROM USERS W, ROLE T, USERROLES S
  WHERE W.USERID = S.USERID
    AND T.ROLEID = S.ROLEID
  GROUP BY W.USERNAME
  
 /* 方法二，使用oracle内置函数 WMSYS.WM_CONCAT */
 SELECT P.USERNAME,
        DECODE(SUBSTR(P.ROS, 0, 1), 1, '管理员') || ',' ||
        DECODE(SUBSTR(P.ROS, 3, 1), 2, '普通用户')
   FROM (SELECT W.USERNAME, WMSYS.WM_CONCAT(T.ROLEID) ROS
           FROM USERS W, ROLE T, USERROLES S
   WHERE W.USERID = S.USERID
            AND T.ROLEID = S.ROLEID
          GROUP BY W.USERNAME) P

问题二

有一张没有主外键关系的数据表，删除所有列相同的重复数据。

1.查询所有列相同的数据行

SELECT T.字段1, T.字段2, T.字段3, COUNT(*)
   FROM TABLE_1 T
  GROUP BY T.字段1, T.字段2, T.字段3
 HAVING COUNT(*) > 1

2.删除上述查询的数据结果集

DELETE FROM TABLE_1 S
  WHERE S.字段1, S.字段2, S.字段3 IN (
    /*查询1语句*/
)

把删除语句里面加入第一个查询的语句，这样就可以删除表中所有列相同的重复数据，如果记录多的话删除会比较慢。

还也可以通过存储过程完成。

建议：先将符合条件的数据记录（即为要删除的数据库纪录）存取到一个临时数据表中，然后再进行删除时就不用进行查询了，提高了数据库的删除速度。

另外大家可能会问，这样删除肯定会存在一个问题，我们不是把所有重复的数据删掉了吗？而我们想保留重复纪录中最新一条的记录，该如何实现呢！

在Oracle中，有个隐藏的列自动rowid，里面会给每条记录唯一的rowid，如果我们想保存最新的一条记录，我们就可以利用这个字段，保存重复数据最大的rowid就可以实现了。

SELECT A.ROWID, 字段1, 字段2
   FROM TABLE_1 A
  WHERE A.ROWID != (SELECT MAX(B.ROWID)
           FROM TABLE_1 B
           WHERE A.字段1 = B.字段1
                AND A.字段2 = B.字段2)

这样就可以把所有的重复记录（rowid不是最大）查找出来。子查询里面的SQL语句是查找出rowid最大的重复数据记录，而外面的是除去 rowid 最大的其他重复数据记录。当我们要删除这些记录时，可以使用语句：

DELETE FROM TABLE_1 A
  WHERE A.ROWID IN A.ROWID != (SELECT MAX(B.ROWID)
                FROM TABLE_1 B
                WHERE A.字段1 = B.字段1
                     AND A.字段2 = B.字段2)

其实还有一道题，就是关于学生、老师、课程、成绩这几张表之间的关系查询，在面试过程中还是会经常遇到，有空还是需要好好的熟练数据库的基本用法，不同数据库有些语法也是不一样的，一定要多学习有助于提高自己。

温馨提示

文章内容如果写的存在问题欢迎留言指出，让我们共同交流，共同探讨，共同进步~~~

文章如果对你有帮助，动动你的小手点个赞，鼓励一下，给我前行的动力。