oracle 常用查询

– 子查询 ,在个别字段需要特殊处理的时候，这种子查询用起来特别好

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’,B.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

修正 decode 后面可以跟子查询，不过要求结果是单条记录

SELECT A.* ,DECODE ((SELECT TLB_TYPE FROM TLB_CARS_LIST_DETAIL WHERE CAR_LIST_ID = A.CAR_LIST_ID) ,’1’,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 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(wm_concat(TAG_NAME)
‘,’,
‘>’) 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#’

– 动态sql拼接，就是相当于单引号内两个单引号表示一个单引号

1 字符串拼接
declare
v_count number default 0;
v_id varchar(36);
v_sql varchar(1000);
begin

dbms_output.put_line(‘测试 sql in 不能接受多值的情况’);
v_id :=’’’7797’’,’’1109’’’;
v_id :=’’’7797’’,’’10000’’’;
dbms_output.put_line(‘v_id: ‘||v_id);
v_sql :=’ select count(1) from tc_attachment where attachment_id in (‘||v_id||’)’;
execute immediate v_sql into v_count/ using v_id/; –
dbms_output.put_line(‘v_count: ‘||v_count||’—-‘||v_sql);

end;

2 函数实现
使用 ASCII 编码，单引号 ‘ 的编码是39，所以拼接字符串可以这样写
str := ‘select * from user where name=’||chr(39)||zhangsan||chr(39)||’ order by id’;

oracle in 查询需要注意

– 存在
select 1 from dual where 1 in (1,null)

– 不存在 ,查询结果为空
select 1 from dual where 1 not in (2,null)

函数

1 – 为空时设置默认值
select nvl(‘ddd’,11) from dual

2 – 为值编码或转义
select decode(1,2,22) from dual

3 – 统计
count
4 – 求和
sum

5 – 分组
group by

6 – 字符串转换函数
to_char
select to_char(sysdate,’yyyy”-“mm-dd HH24”时”:mi:ss’) from dual
7 – 时间转换函数
to_date
select to_date (‘2017-09-01’,’yyyy-mm-dd’) from dual

8 – 差集从集合去掉完全一样的
minus
(select from ) minus (select from )

9 取并集 union all

10 – 去重，对比结果的所有字段
distinct

给列取别名

– oracle 不区分大小写
select 33 as ee from dual
select 33 as ee from dual
select 33 “from” from dual

–# Mysql 默认区分大小写，可以配置为不区分
select 11 LOW from dual
select 11 low from dual
select 11 from from dual