1.取最新的一条记录
Select id
From (select id ,RANK() over(PARTITION BY id order by Savetime desc) as RN
From table_Name
) s where RN=1
2.递归查找 start with connect by prior
----从子节点 找父节点
select * from table_name where start with id = '1' connect by prior parent_id =id;
----从父节点 找子节点
select * from table_name start with id='1' connect by prior id=parentid
---- CONNECT_BY_ROOT 返回当前节点的最顶端节点
----CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
----LEVEL 伪列表示节点深度
----SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔
SELECT (RPAD(' ', 2*(LEVEL-1), '' ) || COMPANY) COMPANY_NAME,
CONNECT_BY_ROOT COMPANY,
CONNECT_BY_ISLEAF, LEVEL ,
SYS_CONNECT_BY_PATH(COMPANY, '/')
FROM TB_COMPANY
START WITH UP_COMPANYID IS NULL CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;
3 declare cursor
declare v_table_en_name varchar2(500);
v_Sql varchar2(1000);
cursor mycur IS
SELECT tbphyisename FROM tablename ;
begin
OPEN mycur; --OPEN CURSOR
FETCH mycur INTO v_table_en_name;--FETCH ONE Record TO RECORD FROM CURSOR
WHILE mycur%FOUND LOOP -- LOOP WHEN HAS RECORDS
v_Sql := 'drop table '|| v_table_en_name;
EXECUTE IMMEDIATE v_Sql ;
FETCH mycur INTO v_table_en_name;--FETCH ONE Record TO RECORD FROM CURSOR AGAIN
END LOOP; --END LOOP
CLOSE mycur;--CLOSE CURSOR
end;
4. 查数据库当前连接数
select count(*) from v$session
--当前连接情况
select sid,serial#,username,status,schemaname,osuser,machine,port,terminal,program,type,module,action,event,service_name from v$session
--当前的进程
select * from v$process
--允许的最大连接数 （v$parameter 参数配置表）
select value from v$parameter where name ='session'