pacer123

2008-06-12

几条有用sql

查找数据库中所有字段以对应的表
select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=''

查每个科目class 分数scro前三名
select id, name, class, scro
from (select row_number() over(partition by class order by scro desc) cnt,
id,
name,
class,
scro
from student) a
where a.cnt <= 3;

查找排序后的前三行
select *
from (select rw.*, rownum
from (select *
from student d
where d.class = 'b'
order by d.scro desc) rw
where rw.id >= 1
order by rw.class desc) n
where rownum <= 3