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;

1 1 a,e,f,g
2 2 b,h,j,i
3 3 c
4 4 d

select * from test_tb_grade;

1 1 bai 语文 78
2 2 bai 数学 89
3 3 bai 英语 90
4 4 xiao 语文 67
5 5 xiao 数学 98
6 6 xiao 英语 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;

对应的查询结果如下：

1 bai 78 89 90
2 xiao 67 98 56

列转行，主要是使用了union

select * from test_tb_grade2;

1 1 bai 67 78 90
2 1 xiao 89 88 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;

1 bai 67
2 bai 78
3 bai 90
4 xiao 88
5 xiao 89
6 xiao 96

参考

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

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