ORCLE 数据库截取小方式

  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   关联修改