有用的SQL

    最近做项目，遇到了几个有用的SQL,拿来大家分享下......

    1 合并：

    select a.bill_id as old_bill_id,a.bill_id,a.business_bill_id,to_char(a.business_date,'YYYY-   MM-DD') as business_date,
     a.org_id,so.org_name,a.operator_id,se.name
 from doa_appl_master a,sys_org so,sys_operator sor,sys_employee se
 where so. org_id = a.org_id
 and a.operator_id=sor.operator_id
 and sor.employee_id = se.employee_id
 and a.business_type_id='27'
 and a.check_mark='1'
 and a.mark='0'
 and a.org_id={ORG_ID}
 union all
 select b.appl_bill_id as old_bill_id,b.bill_id,b.business_bill_id,to_char(b.business_date,'YYYY-MM-DD') as business_date,
     b.org_id,sog.org_name,b.operator_id,see.name
 from doa_master b,sys_org sog,sys_operator sop,sys_employee see
 where b.org_id = sog.org_id
 and b.operator_id=sop.operator_id
 and sop.employee_id = see.employee_id
 and b.business_type_id='30'
 and b.bill_id in (select c.bill_id from doa_detail c where c.mark='3' and c.result='0')
 and b.confirm_mark='1'
 and b.mark='0'
 and b.org_id={ORG_ID}
 order by bill_id Desc

 2 字符截取

  select dom.bill_id,
         dom.business_type_id,
         to_char(dom.business_date, 'YYYY-MM-DD') as business_date,
         dom.appl_bill_id,dom.corres_bill_id,
         dom.operator_id,sog.org_name,se.name
    from doa_master dom,sys_org sog,sys_operator sor,sys_employee se
    where substr(dom.child_org_id,instr(dom.child_org_id,',',-1)+1)=sog.org_id
    and dom.operator_id=sor.operator_id
    and sor.employee_id = se.employee_id
    and dom.business_type_id='28'
    and dom.mark='0'
    and TO_DATE(dom.business_date) <= TO_DATE(sysdate) 
    and TO_DATE(dom.business_date) > TO_DATE(sysdate) - 3
    and dom.parent_org_id={ORG_ID}
    order by bill_id desc