sql 语句集合

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'