27,579
社区成员
发帖
与我相关
我的任务
分享优化SQL查询
请教各位大神，如何优化下面的脚本
select centre.name dtype,
allSum.countAll countAll,
allSum.capacity capacity,
centre.centre centre,
province.province province,
city.city city,
country.country country,
nation.nation nation,
self.self self,
other.other other,
self.self self,
(allSum.countAll - forBackup.forBackup - forRepair.forRepair -
broken.broken) as count,
forRepair.forRepair forRepair,
broken.broken broken,
forBackup.forBackup forBackup
from (select t.id,
t.name,
(case
when info.countAll is NULL then
0
else
info.countAll
end) countAll,
(case
when info.capacity is NULL then
0
else
info.capacity
end) capacity
from t_a_colddevice_type t
left join (select sum(count) countAll,
sum(NVL(refrigeration, 0)) + sum(NVL(freeze, 0)) capacity,
typeid
from t_a_colddevice_info
where 1 = 1
group by typeid) info
on t.id = info.typeid) allSum,
(select t.id,
t.name,
(case
when info.centre is NULL then
0
else
info.centre
end) centre
from t_a_colddevice_type t
left join (select sum(count) centre, typeid
from t_a_colddevice_info
where 1 = 1
and source = 1
and state = 1
group by typeid) info
on t.id = info.typeid
) centre,
(select t.id,
t.name,
(case
when info.province is NULL then
0
else
info.province
end) province
from t_a_colddevice_type t
left join (select sum(count) province, typeid
from t_a_colddevice_info
where 1 = 1
and source = 2
and state = 1
group by typeid) info
on t.id = info.typeid
) province,
(select t.id,
t.name,
(case
when info.city is NULL then
0
else
info.city
end) city
from t_a_colddevice_type t
left join (select sum(count) city, typeid
from t_a_colddevice_info
where 1 = 1
and source = 3
and state = 1
group by typeid) info
on t.id = info.typeid
) city,
(select t.id,
t.name,
(case
when info.country is NULL then
0
else
info.country
end) country
from t_a_colddevice_type t
left join (select sum(count) country, typeid
from t_a_colddevice_info
where 1 = 1
and source = 4
and state = 1
group by typeid) info
on t.id = info.typeid
) country,
(select t.id,
t.name,
(case
when info.nation is NULL then
0
else
info.nation
end) nation
from t_a_colddevice_type t
left join (select sum(count) nation, typeid
from t_a_colddevice_info
where 1 = 1
and source = 5
and state = 1
group by typeid) info
on t.id = info.typeid
) nation,
(select t.id,
t.name,
(case
when info.self is NULL then
0
else
info.self
end) self
from t_a_colddevice_type t
left join (select sum(count) self, typeid
from t_a_colddevice_info
where 1 = 1
and source = 6
and state = 1
group by typeid) info
on t.id = info.typeid
) self,
(select t.id,
t.name,
(case
when info.other is NULL then
0
else
info.other
end) other
from t_a_colddevice_type t
left join (select sum(count) other, typeid
from t_a_colddevice_info
where 1 = 1
and source = 7
and state = 1
group by typeid) info
on t.id = info.typeid) other,
(select t.id,
t.name,
(case
when info.forRepair is NULL then
0
else
info.forRepair
end) forRepair
from t_a_colddevice_type t
left join (select sum(count) forRepair, typeid
from t_a_colddevice_info
where 1 = 1
and state = 2
group by typeid) info
on t.id = info.typeid
) forRepair,
(select t.id,
t.name,
(case
when info.broken is NULL then
0
else
info.broken
end) broken
from t_a_colddevice_type t
left join (select sum(count) broken, typeid
from t_a_colddevice_info
where 1 = 1
and state = 3
group by typeid) info
on t.id = info.typeid
) broken,
(select t.id,
t.name,
(case
when info.forBackup is NULL then
0
else
info.forBackup
end) forBackup
from t_a_colddevice_type t
left join (select sum(count) forBackup, typeid
from t_a_colddevice_info
where 1 = 1
and state = 4
group by typeid) info
on t.id = info.typeid) forBackup
where centre.id = allSum.id
and centre.id = province.id
and centre.id = city.id
and centre.id = country.id
and centre.id = nation.id
and centre.id = self.id
and centre.id = other.id
and centre.id = forRepair.id
and centre.id = broken.id
and centre.id = forBackup.id
...全文
请发表友善的回复…
发表回复
spiritofdragon 2015-09-22
- 打赏
- 举报
优化建议：
1、可读性优化，子查询能用with 的用通用表达式语法，把子查询摘出去
2、isnull函数，去了解下
3、1=1最好不要出现，不然有索引也不一定好使
4、你的多子查询，感觉可以用sum(case ....) 来写，你看看我写的逻辑跟你的一样不？如果有手误的地方你自己改下。
select
t.name dtype
,sum(count) countAll
,sum(NVL(refrigeration, 0)) + sum(NVL(freeze, 0)) capacity --?:NVL是你的自定义函数？
,sum(case when source = 1 and state = 1 then COUNT else 0 end) centre
,sum(case when source = 2 and state = 1 then COUNT else 0 end) province
,sum(case when source = 3 and state = 1 then COUNT else 0 end) city
,sum(case when source = 4 and state = 1 then COUNT else 0 end) country
,sum(case when source = 5 and state = 1 then COUNT else 0 end) nation
,sum(case when source = 6 and state = 1 then COUNT else 0 end) self
,sum(case when source = 7 and state = 1 then COUNT else 0 end) other
,sum(count)- sum(case when state in (2,3,4) then COUNT else 0 end) count
,sum(case when state = 2 then COUNT else 0 end) forRepair
,sum(case when state = 3 then COUNT else 0 end) broken
,sum(case when state = 4 then COUNT else 0 end) forBackup
from t_a_colddevice_type t
left join t_a_colddevice_info info on t.id=info.typeid
group by t.id,t.name
msd2011 2015-09-22
- 打赏
- 举报
这么厉害！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
