oracle 中几个常用sql语句

最新推荐文章于 2019-12-30 11:29:02 发布

让世界在指尖飞舞

最新推荐文章于 2019-12-30 11:29:02 发布

阅读量281

点赞数 2

文章标签： sql plsql 常用sql

本文链接：https://blog.csdn.net/baidu_36699663/article/details/78892448

版权

本文介绍了 SQL 中子查询的应用场景及使用方法，并通过多个实际案例展示了如何利用 decode 函数与 case when 语句进行灵活的数据处理。同时，还探讨了关联查询、自连接、树状查询等高级查询技巧。

摘要由CSDN通过智能技术生成

-- 子查询 ,在个别字段需要特殊处理的时候，这种子查询用起来特别好
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#'