接上帖，格式化了一下， markdown 还是有点问题，有没有优化的建议

select * from (select 'aaa' as batch_no, sum(case when t.result = 0 then 1 else 0 end) final_ng_cnt, count(distinct t.code) as final_total from (select a.item, a.code, a.result, a.datetime, a. recordid, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(nvl(b.batch_no, '@@@'), '@@@') = 0 and b.batch_no = 'aaa') t where t.final_flag = 1) u left join (select 'aaa' as batch_no, sum(case when c.first_rs = 1 then 1 else 0 end) color_cnt, count(c.code) as color_total

 from (select b.batch_no, a.item, a.code, a.result as first_rs, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '00000000')) as first_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(a.item, '0019') > 0 and b.batch_no = 'aaa') c where c.first_flag = 1) v on u.batch_no = v.batch_no left join (select 'aaa' as batch_no, sum(case when instr(g.result, '0') = 0 then 1 else 0 end) weight_pass_cnt, count(g.code) as weight_total from (select k.code, wm_concat(k.item) rank, wm_concat(k.result) as result from (select distinct c. recordid, c.batch_no, c.line_num, c.station_id, c.thread_id, c.item, c.code, c.result, c.datetime, c.first_flag from (select a. recordid, b.batch_no, a.line_num, a.station_id, a.thread_id, a.item, a.code, a.result, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(a.item, '0823') > 0 and b.batch_no = 'aaa' union all select a. recordid, b.batch_no, a.line_num, a.station_id, a.thread_id, a.item, a.code, a.result, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(a.item, '0923') > 0 and b.batch_no = 'aaa' union all select a. recordid, b.batch_no, a.line_num, a.station_id, a.thread_id, a.item, a.code, a.result, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(a.item, '1023') > 0 and b.batch_no = 'aaa') c) k group by k.code) g where instr(g.rank, '0823') > 0 and instr(g.rank, '0923') > 0 and instr(g.rank, '1023') > 0) y on u.batch_no = y.batch_no left join (select 'aaa' as batch_no, sum(count(c.code)) as residual_total, sum(sum(case when c.final_flag = 1 and c.result = 0 then 1 else 0 end)) last_ng_cnt from (select b.batch_no, a.line_num, a.station_id, a.thread_id, a.item, a.code, a.result, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and instr(a.item, '0823') = 0 and instr(a.item, '0923') = 0 and instr(a.item, '1023') = 0 and instr(a.item, '23') > 0 and b.batch_no = 'aaa') c group by c.code) z on u.batch_no = z.batch_no left join (select 'aaa' as batch_no, sum(case when g.first_rs = 1 and g.first_flag = 1 then 1 else 0 end) package_pass_cnt, count(distinct g.code) as package_total from (select 'aaa' as batch_no, a.item, a.code, a.result as first_rs, a.datetime, dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag from fruit_table_a a inner join fruit_table_b b on a. recordid = b.productid where instr(a.code, '000000000000') = 0 and instr(a.code, '9999999') = 0 and b.batch_no = 'aaa' and instr(a.item, '0020') > 0) g) p on u.batch_no = p.batch_no