oracle之sql面试题,oracle sql面试题

1删除重复记录

delete from t  where t.rowid not in ( select  max(rowid)  from t group by name); 2根据2表的关联列，更新1个表的多个字段

update t

set t.address = ( select e.address from e,t where t.name = e.name),

t.phone    = ( select e.phone    from e,t where t.name = e.name); 3选择一段范围内的数据

select * from t where rownum <= 5

minus

select * from t where rownum < 3;

或者

select * from ( select rownum as rn,t.* from t ) s

where s.rn between 3 and 5; 查询工资最高的几个人

select * from (select * from emp  order by salary desc) where rownum<=3;

几个行转列，列转行的测试

SQL> select * from t;

ID NAME

---------- ------------------

1 a

2 b

3 c

4 d

1 e

1 f

1 g

2 h

2 i

2 j

已选择10行。

select id ,wm_concat(name) from t group by id;

11 a,e,f,g

22b,h,j,i

33c

44d

select * from test_tb_grade;

11 bai语文 78

22bai数学89

33bai英语90

44xiao语文67

55xiao数学98

66xiao英语56

下面是使用decode和case when的转换

select user_name,

sum(decode(course,'语文',score,null)),sum(decode(course,'数学',score,null)),sum(decode(course,'英语',score,null))

from test_tb_grade group by user_name;

select user_name,

sum(case when course = '语文' then

score

else null  end ),

sum(case when course = '数学' then

score

else null end),

sum(case when course = '英语' then

score

else null end)

from test_tb_grade group by user_name;

对应的查询结果如下：

1bai 788990

2xiao 679856

列转行，主要是使用了union

select * from test_tb_grade2;

11 bai67 7890

21xiao8988 96

select user_name, cn_score

from test_tb_grade2

union

select user_name, math_score

from test_tb_grade2

union

select user_name, en_score from test_tb_grade2;

1bai 67

2bai78

3bai90

4xiao 88

5xiao 89

6xiao 96

参考

http://blog.csdn.net/zhoubo200/article/details/5338107

http://www.2cto.com/database/201108/100792.html