这是最近在面试过程中遇到的几个关于数据库方面的试题,感觉还是很不错的,经常在项目中也会遇到。
第一题:有三张表用户表(users),角色表(role),用户角色表(userroles),要求查询显示的结果如下
创建表
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 /*创建用户表*/ 2 create table USERS 3 ( 4 USERID NUMBER(4) not null, 5 USERNAME NVARCHAR2(30) 6 ) 7 8 /*创建角色表*/ 9 create table ROLE 10 ( 11 ROLEID NUMBER(2) not null, 12 ROLENAME NVARCHAR2(30) 13 ) 14 15 /*创建用户角色关系表*/ 16 create table USERROLES 17 ( 18 USERID NUMBER(4), 19 ROLEID NUMBER(2) 20 )
通过两种方法可以实现:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 /*方法一,使用分组函数*/ 2 SELECT W.USERNAME, MAX(DECODE(T.ROLEID, 1, '管理员', NULL)) || ',' || MAX(DECODE(T.ROLEID, 2, '普通用户', NULL)) AS "角色" 3 FROM USERS W, ROLE T, USERROLES S 4 WHERE W.USERID = S.USERID 5 AND T.ROLEID = S.ROLEID 6 GROUP BY W.USERNAME 7 8 /*方法二,使用oracle内置函数 WMSYS.WM_CONCAT */ 9 SELECT P.USERNAME, 10 DECODE(SUBSTR(P.ROS, 0, 1), 1, '管理员') || ',' || 11 DECODE(SUBSTR(P.ROS, 3, 1), 2, '普通用户') 12 FROM (SELECT W.USERNAME, WMSYS.WM_CONCAT(T.ROLEID) ROS 13 FROM USERS W, ROLE T, USERROLES S 14 WHERE W.USERID = S.USERID 15 AND T.ROLEID = S.ROLEID 16 GROUP BY W.USERNAME) P
第二题:有一张没有主外键关系的数据表,删除所有列相同的重复数据
1.查询所有列相同的数据行
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 SELECT T.字段1, T.字段2, T.字段3, COUNT(*) 2 FROM TABLE_1 T 3 GROUP BY T.字段1, T.字段2, T.字段3 4 HAVING COUNT(*) > 1
2.删除上述查询的数据结果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 DELETE FROM TABLE_1 S 2 WHERE S.字段1, S.字段2, S.字段3 IN ( /*查询1语句*/ 3 )
这样就可以删除表中所有列相同的重复数据,如果记录多的话删除会比较慢。
建议:先将符合条件的数据记录(即为要删除的数据库纪录)存取到一个临时数据表中,然后再进行删除时就不用进行查询了,提高了数据库的删除速度。
大家都可能会问,这样删除肯定会存在一个问题,我们不是把所有重复的数据删掉了吗?而我们想保留重复纪录中最新一条的记录,该如何实现呢!
在Oracle中,有个隐藏的列自动rowid,里面会给每条记录唯一的rowid,如果我们想保存最新的一条记录,我们就可以利用这个字段,保存重复数据最大的rowid就可以实现了。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 SELECT A.ROWID, 字段1, 字段2 2 FROM TABLE_1 A 3 WHERE A.ROWID != (SELECT MAX(B.ROWID) 4 FROM TABLE_1 B 5 WHERE A.字段1 = B.字段1 6 AND A.字段2 = B.字段2)
这样就可以把所有的重复记录(rowid不是最大)查找出来。里面的SQL语句是查找出rowid最大的重复数据记录,而外面的是除去rowid最大外的其他重复数据记录。当我们要删除这些记录时,可以使用
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 DELETE FROM TABLE_1 A 2 WHERE A.ROWID IN A.ROWID != (SELECT MAX(B.ROWID) 3 FROM TABLE_1 B 4 WHERE A.字段1 = B.字段1 5 AND A.字段2 = B.字段2)
其实还有一道题,就是关于学生、老师、课程、成绩这几张表之间的关系查询,大部分人应该见过,有空还是需要好好的熟练。
今天就到此为止吧,如果大家还有其它好的方法,可以拿出来一块分享,供大家一块学习、交流!!!