这是一个创建于 2495 天前的主题，其中的信息可能已经有所发展或是发生改变。
select u.final_ng_cnt,
v.color_cnt,
v.color_total,
y.weight_total,
y.weight_pass_cnt,
z.residual_total,
z.residual_total - z.last_ng_cnt as residual_pass_cnt,
p.package_total,
p.package_pass_cnt
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
v.color_cnt,
v.color_total,
y.weight_total,
y.weight_pass_cnt,
z.residual_total,
z.residual_total - z.last_ng_cnt as residual_pass_cnt,
p.package_total,
p.package_pass_cnt
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
MeteorCat 2018-01-16 10:05:33 +08:00 via Android  1
卧槽，这陈年代码，我想起以前游戏开发时候用的数据库最开始游戏客户端主程瞎鸡儿建立数据表，后来查询也是这样，一查一动一堆关联表
对于这种如果仅仅做查询的话建议可以跑个定时脚本重新将数据表的大概数据，比如原来 total 字段需要多张表，直接跑脚本写在一张表内部一个 total 字段数据，如果是要查询+修改，那么所需的方法就比较麻烦了 |
 |
2
poupoo OP @MeteorCat 大神主要是为了修改，而且左连接的子查询其实就是两张同样的表 fruit_table_a 和 fruit_table_b 只是筛选条件不太一样，有没有好的修改方法！
|
|
3
MeteorCat 2018-01-16 10:17:03 +08:00 via Android
@poupoo 可以试下直接用数据库的触发器来将修改逻辑写在数据库那边，具体你也可以了解一下，这种代码只能依托数据库本身特性
|
 |
4
crazyneo 2018-01-16 10:30:08 +08:00 1
我屮艸芔茻给跪了……先考虑建视图，再用游标逐条尽量把分析的东西放业务逻辑，如果不行就用存储过程重写这段逻辑，触发器的话我并不推荐，原因是不知道这张业务表的关联性。
|
 |
5
realpg 2018-01-16 10:34:25 +08:00 1
改逻辑 更多东西在代码实现 利用试图和索引 变成各种简单查询
有压力的系统是不能这么写 sql 的 |
|
6
poupoo OP |
 |
7
xkeyideal 2018-01-16 11:05:53 +08:00
这种祖传老代码，就别贴出来了，就算贴，也格式化一下
|
|
9
realpg 2018-01-16 12:51:24 +08:00 1
|
 |
10
wdlth 2018-01-16 12:52:12 +08:00 via iPhone 1
和我们那老系统有得一拼，里面还有各种 outer join，distinct ……
|
 |
13
abusizhishen 2018-01-16 14:33:35 +08:00 via Android
什么鬼，这怎么看
|
 |
14
saulshao 2018-01-16 15:50:32 +08:00 1
我一直有个困惑，在下其实也就会写这样的代码，别的基本都不太会,正好借这个帖子顺便一问:
数据库应用程序基本就是 2 个思路: 1. 写一个特别复杂的查询，把所有的业务逻辑和计算都写在 SQL 里,一次性把结果拿出来显示。好处是只需要查询一次数据库,但是代码可重用性特别差。 2. 将这个查询分解成一系列的 Java/Python 代码,SQL 只负责读取需要的数据,业务逻辑和计算则由应用程序层完成,即所谓的 view 层。好处是代码可重用性比较好,但是可能查询数据库的次数会成(数十)倍增加。 选择这 2 种策略的原则到底是什么？有什么指导性的理论或者是书，又或者是最佳实践的文章不？ 楼主这贴其实在我看来更适合后面的方法，因为从数据库返回的记录集很小，查询数据库的次数其实也很有限。即使分开，也没有什么致命的性能问题。 |
 |
15
noahzh 2018-01-16 16:27:51 +08:00 2
@saulshao 那是没有遇到海量数据，数据分库情况下，这样及联查询是没有办法使用的。在数据库里做计算，是一种深坑，一旦数据库没有办法满足你的性能需求时候，就会发现你要改逻辑了，那时候你面临着业务压力与性能压力。性能问题不要看当前，要看业务类型。现在可以满足不代表未来也能满足，但是也不要过度设计。还有数据库返回记录集小是一个没有意义的指标，因为数据库真正瓶颈是查询复杂度。
|
|
16
poupoo OP 目前来看并没有用到很新的技术，而且也没有转移到 python 或者 Java 的可能，老板就扔了一个包，让你整
|
 |
17
lygmqkl 2018-01-16 17:45:30 +08:00 1
没有架构，没有产品吗？ 天。。。
|
|
18
wdlth 2018-01-16 21:33:28 +08:00 1
@poupoo 尽量减少表关联，我看上面有很多的子查询，排名函数、聚合函数，如果条件允许，可以分解成多步简单的查询，再在程序里面进行业务逻辑的实现。
|
 |
19
odirus 2018-01-16 21:55:48 +08:00 via Android
搭车求问，大家都是怎么规范别名，临时表 命名的？
|
|
20
odirus 2018-01-16 21:56:21 +08:00 via Android 1
我都 a b c 命名有恐惧症
|
 |
21
l00t 2018-01-16 22:22:04 +08:00 1
说 SQL 优化呢，你得先说数据库是什么，然后贴个目前的执行计划，这个是最基本的了吧…… 不然从何说起呢……
从语法看，你这个多半是 Oracle 吧。我建议你把每个子查询单独拉出来执行下，看看哪个特别慢，找到性能瓶颈。 没有执行计划，说不了太多。单从语句看，y 那段问题比较大。没有必要写那么复杂的，你可以精简出更有效率的语句来实现同样的效果。y 里面的那个 dense_rank 更是完全看不出作用。 你这个语句里所有的 inner join 里面的 where 貌似都可以改成 and，或者直接改成不需要 inner join 的模式。我看你的 inner join 貌似无非只是确认下存在而已，对于 b 表的字段一个都没用上。 left join 对你这个语句的影响应该等于 0。你的 u,v,y,z,p 全都只有一行记录，这种 left join 能有啥关系？ |
|
22
poupoo OP |
Select Language