Question

sql子查询调用另一个子查询结果

如select sum(decode(sign((select sum(decode(t.ywid,cb.id,1,0)) from pap_records t)-0),1,1,0))||'不正常数量'||';'
||sum(decode(sign((select count(*) from pap_records t where t.ywid = cb.id)-1),-1,1,0))||'正常数量'||';'
||sum(decode(sign((select count(*) from pap_records t where t.yclx='2' and t.ywid = cb.id)-0),1,1,0))||'风险数量'||';'
||sum(decode(sign((select count(*) from pap_records t where t.yclx='1' and t.ywid = cb.id)-0),1,1,0))||'异常数量'||';'
||sum(decode(sign((select count(*) from pap_ask ta where ta.cllx='1' and exists (select '1' from pap_records t where ta.pap_records_id=t.id and t.ywid = cb.id))-0),1,1,0))||'督办数量'
from ct_baseinfo cb 中间的几个子查询几乎都是重复查询的 能不能把结果一次性写在一个子查询里边 其他的都调用这个结果 或者求大神给这个sql给优化下 这一句效率太低

Answer 1

由于不了解各表之间的关系，所以只能提些建议
1、ct_baseinfo里的id都是唯一的？
2、第一个sum里的子查询由于跟ct_baseinfo无关，所以可以单独提出来作为一个子查询，以后再跟ct_baseinfo作full join(由于只有一条记录，其实也就相当于补一列）

3、第2、3、4个子查询可用以下语句替代：
select count(*) 正常数量, sum(case when t.yclx='1' then 1 end) 风险数量, sum(case when t.yclx='2' then 1 end) 异常数量 from ct_baseinfo cb left join pap_records t on cb.id=t.ywid group by cb.id
4、不知pap_ask中pap_record_id是否有重复，有重复则先定义一个聚集后的子查询
总结如下：
with t3 as (select pap_record_id, count(*) c1 from pap_ask2 where cllx='1' group by pap_record_id， t4 as (select count(*) c2 from pap_records)
select t.id, sum(t3.c1) 不正常数量, count(*) 正常数量, sum(case when t1.yclx='1' then 1 end) 风险数量, sum(case when t1.yclx='2' then 1 end) 异常数量, sum(t4.c2) 督办数量
from ct_baseinfo t left join pap_recrods t1 on t.id=t1.ywid left join t3 on t1.id=t3.pap_record_id full join t4

Answer 2

不知道是不是这个意思，你可以测试一下
select
count(distinct t.ywid)||‘不正常数量'
||count(distinct case when t.ywid is null then cb.id else null end) ||'正常数量'||';'
||count(distinct case when t.yclx='2' then t.ywid else null end)||
'风险数量'||';'
||count(distinct case when t.yclx='1' then t.ywid else null end)||
'异常数量'||';'
||count(distinct case when ta.cllx='1' and t.id is not null and cb.id is not null then ta.cllx else
null end)||'督办数量'
from
ct_baseinfo cb
left join
pap_records t on cb.id=t.ywid
left join
pap_ask ta on ta.pap_records_id=t.id

Answer 3

select sum(decode(sign((select sum(decode(t.ywid, cb.id, 1, 0))
from pap_records t) - 0),
1,
1,
0)) || '不正常数量' || ';' ||
sum(decode(sign(sum(e.cnt) - 1),
-1,
1,
0)) || '正常数量' || ';' ||
sum(decode(sign(sum(decode(yclx,'2',cnt,0)) - 0),
1,
1,
0)) || '风险数量' || ';' ||
sum(decode(sign(sum(decode(yclx,'1',cnt,0)) - 0),
1,
1,
0)) || '异常数量' || ';' ||
sum(decode(sign((select count(*)
from pap_ask ta
where ta.cllx = '1'
and exists (select '1'
from pap_records t
where ta.pap_records_id = t.id
and t.ywid = cb.id)) - 0),
1,
1,
0)) || '督办数量' from (select count(*) as cnt,yclx from pap_records t where t.ywid = cb.id group by yclx) e

Answer 4

如果是oracle的可以用with .. as( )

追问

能不能帮忙改下 对oracle一些函数不太熟悉 或者举个例子也行