oracle 中几个常用sql语句

-- 子查询 ,在个别字段需要特殊处理的时候，这种子查询用起来特别好
 select a.*,
        (select max(b.request_attachment_id)
           from tlb_request_to_attachment b
          where b.p_attachment_id = a.attachment_id)
   from tc_attachment a
-- decode 与 case  when 三表关联 
-- 可以看成都是对值进行编码,都只是单列处理,不同的是，case when 可以跟子查询，而decode 不能 ，因为decode 是函数
 
SELECT  A.* ,DECODE (A.TLB_TYPE ,'1',SELECT AA AS AA  FROM DUAL /*B.USER_NAME*/ ,'2',C.NAME,NULL) FROM TLB_CARS_LIST_DETAIL   A  
LEFT JOIN TLB_REQUEST_PERSONAL B  ON  A.TLB_ID = B.TLB_ID 
LEFT JOIN TLB_REQUEST_BIG_CUSTOMER C  ON A.TLB_ID = C.TLB_ID 


SELECT CASE UNION_ID WHEN '700' THEN '1' WHEN '710' THEN (SELECT PERSON_ID  FROM   TB_CFS_AUDITGROUP_PERSON_UNION  U  WHERE U.UNION_ID = A.UNION_ID   )   ELSE '3' END  FROM TB_CFS_AUDITGROUP_PERSON_UNION  A 


-- 两表关联查询  存在不存在


SELECT A.*,CASE  when B.PERSON_ID IS NULL THEN '0'  ELSE  '1'  END  IS_IN   FROM  TB_CFS_AUDIT_GROUP_PERSON  A  LEFT JOIN  TB_CFS_AUDITGROUP_PERSON_UNION  B  ON ( A.PERSON_ID=B.PERSON_ID 
AND  GROUP_ID =211 )


SELECT A.*,CASE  when A.PERSON_ID = ANY (SELECT PERSON_ID FROM TB_CFS_AUDITGROUP_PERSON_UNION U WHERE U.GROUP_ID =B.GROUP_ID )   THEN '0'  ELSE  '1'  END  IS_IN   FROM  TB_CFS_AUDIT_GROUP_PERSON  A  LEFT JOIN  TB_CFS_AUDITGROUP_PERSON_UNION  B  ON ( A.PERSON_ID=B.PERSON_ID 
AND  GROUP_ID =211 )


-- 自连接 去重 或是 exist 
-- 查找只有单条记录无对应的下一步记录，第二种是自连接实现的剔除，有点low




select *
  from tba_agent_bail_detail a
 where not exists
 (select 1
          from tba_agent_bail_detail
         where a.business_id = business_id
           and a.business_type = business_type
           and a.bail_detail_id != bail_detail_id
           and a.bail_type = bail_type
           and a.bail_amount = bail_amount)
   and a.bail_type in ('3', '4')
   and a.bail_flag = '1'
   and business_type in
       ('TLB_REQUEST_PERSONAL', 'TLB_REQUEST_BIG_CUSTOMER');


select *
  from (
        
        select a.bail_detail_id,
                a.business_type,
                a.business_id,
                a.bail_type,
                a.bail_flag,
                a.bail_amount,
                a.agent_id,
                a.remark,
                to_char(a.create_date, 'yyyy-mm-dd') create_date,
                (case
                  when a.bail_type = 3 then
                   b.GPS_DEPOSIT_LEVERAGE
                  else
                   b.ACCESS_DEPOSIT_LEVERAGE
                end) AS "DEPOSIT_LEVERAGE",
                c.agent_name,
                d.user_name,
                d.tlb_code,
                d.contract_number,
                to_char(d.apply_final_time, 'yyyy-mm-dd') apply_final_time,
                to_char(d.apply_down_time, 'yyyy-mm-dd') apply_down_time,
                d.vehicle_category,
                d.APPLY_STATUES
          from tba_agent_bail_detail a
          left join tba_agent_bail b on b.agent_id = a.agent_id
          left join tdl_agent_apply c on c.agent_id = a.agent_id
          left join TLB_REQUEST_PERSONAL d on d.tlb_id = a.business_id
         where a.business_type = 'TLB_REQUEST_PERSONAL'
           and a.bail_flag in ('1', '2')
        
        union all
        select a.bail_detail_id,
               a.business_type,
               a.business_id,
               a.bail_type,
               a.bail_flag,
               a.bail_amount,
               a.agent_id,
               a.remark,
               to_char(a.create_date, 'yyyy-mm-dd') create_date,
               (case
                 when a.bail_type = 3 then
                  b.GPS_DEPOSIT_LEVERAGE
                 else
                  b.ACCESS_DEPOSIT_LEVERAGE
               end) as "DEPOSIT_LEVERAGE",
               c.agent_name,
               d.name as user_name,
               d.tlb_code,
               d.contract_number,
               to_char(d.apply_final_time, 'yyyy-mm-dd') apply_final_time,
               to_char(d.apply_down_time, 'yyyy-mm-dd') apply_down_time,
               d.vehicle_category,
               d.APPLY_STATUES
          from tba_agent_bail_detail a
          left join tba_agent_bail b on b.agent_id = a.agent_id
          left join tdl_agent_apply c on c.agent_id = a.agent_id
          left join TLB_REQUEST_BIG_CUSTOMER d on d.tlb_id = a.business_id
         where a.business_type = 'TLB_REQUEST_BIG_CUSTOMER'
           and a.bail_flag in ('1', '2')) t
 where to_number(trim(t.APPLY_STATUES)) >= 419
      
   AND bail_detail_id NOT IN
       (SELECT A.BAIL_DETAIL_ID
          FROM TBA_AGENT_BAIL_DETAIL A
          JOIN TBA_AGENT_BAIL_DETAIL B ON A.BUSINESS_ID = B.BUSINESS_ID
                                      AND A.BUSINESS_TYPE = B.BUSINESS_TYPE
                                      AND A.BAIL_TYPE = B.BAIL_TYPE
                                      AND A.BAIL_AMOUNT = B.BAIL_AMOUNT
                                      AND A.BAIL_DETAIL_ID <>
                                          B.BAIL_DETAIL_ID
                                      AND A.BAIL_FLAG = '1')
      
   AND AGENT_ID = 1533
   AND BAIL_FLAG = '1'
 order by business_type, to_number(business_id), bail_flag, bail_amount


-- 树 
-- 查询父节点  根据子节点查找父节点 
select *
  from tb_value_list_details
 start with (  details_id = '1361' )
connect by prior   p_father_id=  details_id




-- 查询子节点 根据父节点查找子节点 


 select *
  from tb_value_list_details
 start with ( details_id = '1361' ) 
connect by    p_father_id=  prior details_id


-- 拼接字符串 成为一列  ,类似于列转行 ,两个函数都有字符数限制,但listagg还可以实现排序 拼接 
SELECT REPLACE(listagg(TAG_NAME, ',') within
                          group(order by to_number(tag_up_id)),
                          ',',
                          '>') AS TAG_NAME
             FROM TCM_TAG  a  
            where tag_level > 1
            START WITH TAG_ID = '52'
           CONNECT BY PRIOR TAG_UP_ID = TAG_ID
               
               
SELECT REPLACE(listagg(TAG_NAME, ',') within
      group(order by to_number(tag_up_id)),
      ',',
      '>') AS TAG_NAME
FROM TCM_TAG a
where tag_level > 1
START WITH TAG_ID = '52'
CONNECT BY PRIOR TAG_UP_ID = TAG_ID
               
--查询被锁对象和解锁
select object_name,machine,s.sid,s.serial#,L.OS_USER_NAME
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;


alter system kill session 'sid,serial#'