Oracle SQL查询1--171220
--1、etl_extract_log表
select a1 操作次数,a2 行数,a3 库,tt2.log_level 成功数,(a1-tt2.log_level) 失败数 from (
select count(tt.op_object) a1,sum(tt.rowcount) a2,tt.app_name a3,sum(tt.log_level)a4
from (
select t.op_object,t.rowcount,t.log_level,t.log_time,t1.table_name,t1.app_name
from ETL_EXTRACT_LOG t,ETL_TABLE_LIST t1
WHERE trunc ( log_time ) = trunc ( sysdate ) and t.op_object = t1.table_name
) tt
group by tt.app_name
)tt1
left join (
select count(a.log_level)log_level,app_name from (
select t.op_object,t.rowcount,t.log_level,t.log_time,t1.table_name,t1.app_name
from ETL_EXTRACT_LOG t,ETL_TABLE_LIST t1
WHERE trunc ( log_time ) = trunc ( sysdate ) and t.op_object = t1.table_name
) a
where a.log_level = '1'
group by a.app_name
)tt2
on tt1.a3=tt2.app_name
order by 5 desc
查询结果:
操作次数 行数 库 成功数 失败数
50 10171656 JSGL 33 17
6 162 JCQZDB1 6 0
48 25439547 ZXXS 48 0
16 2631139 ZZXS 16 0
16 124867 XS 16 0
14 227377 ZB 14 0
注:记录开始写稍微复杂一点的SQL,其实是相对,后来人肯定觉得很简单。