数据库语句记录

oracle 查询字段所在的表

select owner, table_name

from dba_tab_columns

where lower(column_name)='c_auth_field_value';

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

查看死锁的session

select session_id from v$locked_object;
SELECT * FROM v$session where sid = 158;
ALTER SYSTEM KILL SESSION '158,15';

比方有一张流水号的表

流水号名 流水号
a1          00001
a1          00002
a1          00004
a1          00007
a1          00008
a1          00009

现在就是想出个报表，显示如下

流水号名  开始      结束
a1            00001   00002
a1            00004   00004
a1            00005   00007

也就是说按照流水号是否连续分组 取得连续的流水号范围.

select
 *
from TXIN_TRX_REFUSING A, (
select C_REF_NAME,MIN(C_REF_NO) AS FAILED_FROM,MAX(C_REF_NO) AS FAILED_TO  from TXIN_TRX_REFUSING T1
START WITH NOT EXISTS(SELECT 1 FROM TXIN_TRX_REFUSING WHERE to_number(C_REF_NO)=to_number(T1.C_REF_NO)-1 AND C_REF_NAME=T1.C_REF_NAME)
CONNECT BY PRIOR to_number(C_REF_NO)=to_number(C_REF_NO)-1 AND PRIOR C_REF_NAME=C_REF_NAME
GROUP BY CONNECT_BY_ROOT(C_REF_NO),C_REF_NAME
) B 
where A.C_REF_NO = B.FAILED_FROM AND A.C_REF_NAME = B.C_REF_NAME