这是一个创建于 2540 天前的主题，其中的信息可能已经有所发展或是发生改变。
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, '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 b.batch_no = 'aaa'
and instr(a.item, '0020') > 0) g) p on u.batch_no =
p.batch_no
 |
1
onsale 2018-01-16 15:27:47 +08:00 via Android
大段代码还是贴 gist/pastebin 比较好
|
Select Language