Select Gnssj.Fl_Sjid Sjid
,Gnssj.Fl_Id Xjid
From t_Xt_Gnsflb Gnssj
Start With Gnssj.Fl_Id = '60673'
Connect By Prior Gnssj.Fl_Sjid = Gnssj.Fl_Id;
Select Gnssj.Fl_Sjid Sjid
,Gnssj.Fl_Id Xjid
From t_Xt_Gnsflb Gnssj
Start With Gnssj.Fl_Id = '10203'
Connect By Prior Gnssj.Fl_Id = Gnssj.Fl_Sjid ;
获取上下级ID
Select Gnssj.Fl_Sjid Sjid
,Gnssj.Fl_Id Xjid
From t_Xt_Gnsflb Gnssj
Start With Gnssj.Fl_Id = '10203'
Connect By Prior Gnssj.Fl_Id = Gnssj.Fl_Sjid ;
SELECT SUBSTR('HT-201212358142306',0,11) FROM DUAL
INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
曹探 14:37:21
string substr ( string string, int start [, int length])
参数1:处理字符串
参数2:截取的起始位置(第一个字符是从0开始)
参数3:截取的字符数量
//找最后
SELECT SUBSTR('1@45666', INSTR('1@45666', '@',-1)+1) FROM DUAL;
SELECT SUBSTR('30002-5-6-8', INSTR('30002-5-6-8', '-',1,3) +1) FROM DUAL
SELECT SUBSTR('30002-5-6-8-56-111-2222', 0,INSTR('30002-5-6-8-56-111-2222', '-',-1)-1) FROM DUAL;
//找最前
SELECT SUBSTR('30002-5-6-8', INSTR('30002-5-6-8', '-',-1,1) +1,LENGTH('30002-5-6-8') ) FROM DUAL
//定位找下一个数据
SELECT SUBSTR('30002-5-6-8',INSTR('30002-5-6-8','5',1,1)+2,1) FROM dual;
//定位数据找前面数据
SELECT SUBSTR('30002-5-6-866-11-33', 0,INSTR('30002-5-6-866-11-33', '33')-2) FROM DUAL
//定位找后面数据
SELECT SUBSTR('354-54-5454-123',INSTR('354-54-5454-123', '54-')+length('54-')) FROM DUAL
//永远找首位信息
select SUBSTR('30002-5-6-866-11-33', 0,INSTR('30002-5-6-866-11-33', '-',-1,1)-1) FROM DUAL
//截止到第二个符号前的数据
SELECT SUBSTR('30002-5-6-8',1,INSTR('30002-5-6-8','-',1,2)-1) FROM DUAL
//找第一个
SELECT SUBSTR('30002-5-6-8', 0, INSTR('30002-5-6-8', '-') - 1) FROM DUAL
//倒数第二个
select SUBSTR(SUBSTR('11111@22222@33333@44444',INSTR('11111@22222@33333@44444','@', -1, 2)+1),0,length(SUBSTR('11111@22222@33333@44444',INSTR('11111@22222@33333@44444','@', -1, 1),length('11111@22222@33333@44444')))-1 ) FROM DUAL;
//从倒数到倒数第二个符号的数据
select SUBSTR('11111@22222@33333@44444',INSTR('11111@22222@33333@44444','@', -1, 2)+1) FROM DUAL;
//查询多条件查询找一个记录的数据
select sx.sx_mc from t_dx_sxb sx
where sx.sx_id in(
Select sx_id from (
Select a.sx_id from t_dx_sxzjb a where a.fs_id='1095633@1050739'
union all
Select b.sx_id from t_dx_sxzjb b where b.fs_id='1095633'
) t_dx_sxzjb
//替换
select replace ('111222333444','222','888') from dual;
//获取特殊符号出现的次数
select length('a@b@c@')-length(replace('a@b@c@','@','')) from dual
//相除
select MOD(65,50) from dual --取余
select trunc( 65/33) from dual -- 取整 trunc (1.9) = 1
select ceil(65/60) from dual -- 取整 ceil(1.1) = 2
select trunc(length('1094666@1094645@1083576@45@55')-length(replace('1094666@1094645@1083576@45@55','@','')))/2 from dual
SELECT djk.djk_id,
CASE WHEN sxz.xh = '1'
THEN (select sxz.sx_z from T_DJK djk, T_SXZB sxz where djk.djk_id=sxz.fs_id and sxz.sx_id='403011' and djk.djk_id='30182' )
END,
CASE WHEN sxz.xh = '2'
THEN (select sxz.sx_z from T_DJK djk, T_SXZB sxz where djk.djk_id=sxz.fs_id and sxz.sx_id='40310' and djk.djk_id='30182')
END,
CASE WHEN sxz.xh = '3'
THEN (select sxz.sx_z from T_DJK djk, T_SXZB sxz where djk.djk_id=sxz.fs_id and sxz.sx_id='2307' and djk.djk_id='30182')
END,
CASE WHEN sxz.xh = '4'
THEN (select sxz.sx_z from T_DJK djk, T_SXZB sxz where djk.djk_id=sxz.fs_id and sxz.sx_id='403012' and djk.djk_id='30182')
END
from T_DJK djk, T_SXZB sxz where djk.djk_id=sxz.fs_id and djk.djk_id='30182'
//学习SQL网址
http://www.w3school.com.cn/sql/sql_select_into.asp
//截取最后一个字符
select substr('小鹿,小凯,',1,length('小鹿,小凯,')-1) from dual
select temp.sx_z,temp1.* from( select sx_id,sx_z,dw_z from t_dx_sxzb where fs_id='1403588') temp left join (select z.FS_ID ,
z.SX_ID,
'1402848@888' || '@' ||
SUBSTR(z.FS_ID,
INSTR(z.FS_ID, '1402848@1402842' || '@') +
length('1402848@1402842' || '@')) pc
from t_Dx_Sxzb z
where z.dx_key = '42010'
and z.fs_id like '1402848@1402842@%'
and instr('@' || z.fs_id || '@', '@' || z.sx_id || '@') = 0) temp1 on temp.sx_id=temp1.sx_id
select count(fs_id), fs_id from t_dx_sxzb where sx_id='9033' group by fs_id having(count(fs_id))>1
//oracle中怎么获取一串字符串中的数字
select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual;
select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual;
SELECT TRIM(TRANSLATE('ASDFA234SDF','ABCDEFGHIJKLMNOPQRSTUVWXYZ',' ')) FROM DUAL;
//获取最大的数字
select MAX(SUBSTR(DX_ID,INSTR(DX_ID, 'DG')+length('DG'))) from t_dx_divb where DX_ID LIKE 'A@B%' AND instr(DX_ID,'DG')!=0
//orcle 时间转换成数字 数字转换成时间
SELECT to_date( max(to_number(to_char(lrsj,'yyyymmddhhmiss')) ),'yyyy-mm-dd,hh24:mi:ss') as lrsj
FROM t_dxb
where lrsj <
to_timestamp('2012-11-16 11:12:12', 'yyyy-MM-dd HH24:MI:SS')
order by lrsj desc
//日期转换(13-11月-12 04.38.12.000000 下午转换成2012-11-13 16:38:12)
select to_char(to_timestamp('14-11月-12 04.38.57.000000 下午','dd-mon-rr hh.mi.ss.ff am'),'yyyy-mm-dd hh24:mi:ss') from dual;
//日期转换(2012-11-13 16:38:12转换成13-11月-12 04.38.12.000000 下午)
select to_char(to_timestamp('2012-11-13 16:38:12','yyyy-mm-dd hh24:mi:ss'),'dd-mon-rr hh.mi.ss.ff am') from dual;
//学习
select * from (select d.dx_id||'@' xdx_id, d.* from t_dx_divb d) t where t.xdx_id like '1046293@%'
//拼凑字段只
select wm_concat(xgr) from (select xgr from t_dx_divb where dx_id like '1046293%' group by xgr )
select div.* from t_dx_divb div where dx_id like '1046293%' and instr((select wm_concat(xgr) from (select xgr from t_dx_divb where dx_id like '1046293%' group by xgr )),div.div_id)=0
//生效时间
select substr(dx.xgsj, INSTR(dx.xgsj, '9037', -1, 1) + 5) || '*' from t_dxb dx
where instr(dx.xgsj, '*9037/') != 0
and dx.dx_id='1046293'
SELECT to_char(to_timestamp(SUBSTR((A), 0,INSTR((A), '*') - 1), 'dd-mon-rr hh.mi.ss.ff am'),'yyyy-mm-dd hh24:mi:ss') FROM DUAL
找头查询
select fs_id from t_sx_sfzb where SF_ZID in(
select SF_ZID from (select '@' || a.fs_id || '@' fsid,a.SF_ZID from t_sx_sfzb a) temp where SUBSTR(temp.fsid, 1, INSTR(temp.fsid, '@',1,2))='@1089779@'
)
//处理过的
select fs_id from t_dx_sxzjb where sx_zjid in(
select max(to_number(sx_zjid)) from (select sx_zjid,fsid,xgr,xgsj,zt,dx_key,sx_id from (select '@' || a.fs_id || '@' fsid,a.sx_zjid,a.xgr,a.xgsj,a.zt,a.dx_key,a.sx_id from t_dx_sxzjb a) temp where SUBSTR(temp.fsid, 1, INSTR(temp.fsid, '@',1,2))='@1089779@'
) temp1 where instr(xgr,'@')=0 group by sx_id
)
//找中间测试方法
public static void main(String[] args) {
String test = "1105354@1094645@1083576";
String a = "(select trunc(length('" + test + "')-length(replace('"+ test + "','@','')))/2 from dual) ";
String b = "(select substr('" + test + "', instr('" + test + "','@',1,"
+ a + ") ) || '@' from dual)";
String c = "SELECT SUBSTR(" + b + ", 1, INSTR(" + b
+ ", '@',1,3)) FROM DUAL";
System.out.println(c);
}
/**取中**/
public static void main(String[] args)throws Exception {
String aa="854@454@856@1104521@8745@965@542";
String result="";
if(aa.indexOf("@")!=-1){
String[] aaArr=aa.split("@");
int aaLength=aaArr.length;//aa的@个数
result=aaArr[aaLength/2];
}else{
result=aa;
}
System.out.println("result=="+result);
}
//取中
public static void main(String[] args) {
String tests="A@B@C@D@C";
int count=CZDX_SubStr_Util.hqStrCount(tests, "@");
String dxid=CZDX_SubStr_Util.strSub(tests, "@", count/2, count/2+1);
System.out.println(dxid);
//取别名属性
String dxid=CZDX_SubStr_Util.strSub(tests, "@", count/2+1, -1);
//取最后一个别名
strSub(tests, "@", hqStrCount(tests, "@"), -1)
}
//删除重复数据
delete from aaa where fs_id || sx_id || sf_id in (select temp.fs_id||temp.sx_id||temp.sf_id from (select fs_id,sx_id,sf_id from aaa group by fs_id,sx_id,sf_id having count(*)>1
)temp )
and id not in (select min(id) from aaa group by fs_id,sx_id,sf_id having count(*)>1)
//修改时间
update t_dxb set xgsj = '9036/'|| (
select to_char(to_timestamp((select SUBSTR(xgsj,INSTR(xgsj,'/',-1)+1) from DUAL),'dd-mon-rr hh.mi.ss.ff am'),'yyyy-mm-dd hh24:mi:ss') from DUAL
) where xgsj like '%月%' and xgsj like '%9036%' and xgsj not like '%*%'
select (SAAS3_SEQ.NEXTVAL) kid from dual connect by rownum<=111111111
for(int i=array.length-1;i>=0;i--){
System.out.println(sss+"=="+array[i]);
}
select * from t_dxb,(with a as (select 'aaa,1014769,1014797,cc,' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^,]+'))) tempTable where tempTable.id=t_dxb.dx_id(+);
SELECT *
FROM ( SELECT DENSE_RANK() OVER ( ORDER BY A.oprTime DESC ) AS ROW_NUMBER ,
*
FROM FLOW_T_FLOW_TASK_RELATION a
WHERE flowID = 'd4557c7d-4b3f-4db5-b5e7-4135cb660f69'
AND taskID = 'K00202-006'
) AS a
WHERE ROW_NUMBER = '2'
//获取最新时间记录
SELECT a.OPRTIME,
a.taskID,
a.FLOWID,
c.status,
taskIndex,
b.taskName
FROM (SELECT Max(OPRTIME) OPRTIME,
taskID,
FLOWID
FROM dbo.FLOW_T_FLOW_TASK_RELATION
WHERE taskID != 'K99999-999'
GROUP BY FLOWID,
taskID) a
LEFT JOIN FLOW_T_TASK_DEF b
ON a.taskID = b.taskID
INNER JOIN FLOW_T_FLOW_TASK_RELATION c
ON a.flowID = c.flowID
AND a.taskID = c.taskID
AND a.OPRTIME = c.oprTime
//自己关联自己最新记录(可以取自己任意字段)
select * from FLOW_T_FLOW_TASK_RELATION a inner JOIN
(
SELECT
MAX ( relation.oprTime) oprTime,
relation.taskID,
relation.flowID,
def.taskName
FROM
dbo.FLOW_T_FLOW_TASK_RELATION relation
INNER JOIN FLOW_T_TASK_DEF def ON def.taskID=relation.taskID
WHERE oprResult is not null and relation.oprResult!='K00805'
GROUP BY
relation.flowID,
relation.taskID,
def.taskName
)b
on a.flowID=b.flowID and a.taskID=b.taskID and a.oprTime=b.oprTime
WHERE b.taskName='项目经理<br/>审核' or b.taskName='项目经理审核'
and a.oprResult!='K00805' and a.oprResult is not null
----begin 关联修改
update areaCode set areaCode.storeName=store.storeName
from [BIZ_T_STORE_INFO] store
inner join BIZ_T_STORE_CHANGE_AREACODE_INFO areaCode on store.officialStoreCode=areaCode.officialStoreCode
----end 关联修改
ORCLE 数据库截取小方式
于 2015-05-19 15:53:46 首次发布