如题。
看到这堆代码头大，子查询多，效率低，如果明白业务逻辑我就重写了
实在不想花时间调，那位想练练手就上吧。O(∩_∩)O~select distinct sp.serv_product_id servProductId,
sp.equip_no equipNo,
sp.serv_id servId,
s.serial_nbr serialNbr,
sp.cust_id custId,
c.cust_no custNo,
sp.product_id productId,
to_char(sp.created_date, 'YYYY-MM-DD HH24:MI:SS') createdDate
from serv_product sp,
serv s,
product p,
cust c,
tem_equipment te,
tem_equip_modal tem,
tem_equip_type tet
where s.serv_id = sp.serv_id
and p.product_id = sp.product_id
and s.cust_id = c.cust_id
and sp.equip_no = te.equip_no
and tem.equipmodal_no = te.equipmodal_no
and tet.equiptype_no = tem.equiptype_no
and (select distinct spt.serv_id
from serv_product spt,
product p,
product_pack_plan pp,
product_rent_plan pr
where p.pack_plan_id = pp.pack_plan_id
and spt.serv_id = sp.serv_id
and spt.cust_id = sp.cust_id
and spt.product_id = p.product_id
and pp.price_plan_id = pr.price_plan_id
and p.basic_flag = '1'
and pr.rate = 0) is not null --基本包不是免费的
and sp.product_state in ('1', '0')
and sp.equiptype_no in ('1', '2')
and tet.equiptype_no in ('1', '2')
and c.state = '70A'
and s.serial_nbr = '2'
and s.state in ('F0A', 'F0P')
and sp.buy_type = '2'
and sp.cust_id in (select distinct sp2.cust_id
from serv_product sp2,
serv s2,
product p2,
cust c2,
tem_equipment te2,
tem_equip_modal tem2,
tem_equip_type tet2
where s2.serv_id = sp2.serv_id
and p2.product_id = sp2.product_id
and s2.cust_id = c2.cust_id
and sp2.equip_no = te2.equip_no
and tem2.equipmodal_no = te2.equipmodal_no
and tet2.equiptype_no = tem2.equiptype_no
and (select distinct spt2.serv_id
from serv_product spt2,
product p2,
product_pack_plan pp2,
product_rent_plan pr2
where spt2.serv_id = sp2.serv_id
and spt2.cust_id = sp2.cust_id
and p2.pack_plan_id = pp2.pack_plan_id
and spt2.product_id = p2.product_id
and pp2.price_plan_id = pr2.price_plan_id
and p2.basic_flag = '1'
and pr2.rate <> 0) is not null
and sp2.product_state in ('1', '0')
and sp2.equiptype_no in ('1', '2')
and tet2.equiptype_no in ('1', '2')
and c2.state = '70A'
and s2.serial_nbr = '1'
and s2.state in ('F0A', 'F0P')
and sp2.buy_type = '1') -- 赠送的基本包都是免费的
看到这堆代码头大，子查询多，效率低，如果明白业务逻辑我就重写了
实在不想花时间调，那位想练练手就上吧。O(∩_∩)O~select distinct sp.serv_product_id servProductId,
sp.equip_no equipNo,
sp.serv_id servId,
s.serial_nbr serialNbr,
sp.cust_id custId,
c.cust_no custNo,
sp.product_id productId,
to_char(sp.created_date, 'YYYY-MM-DD HH24:MI:SS') createdDate
from serv_product sp,
serv s,
product p,
cust c,
tem_equipment te,
tem_equip_modal tem,
tem_equip_type tet
where s.serv_id = sp.serv_id
and p.product_id = sp.product_id
and s.cust_id = c.cust_id
and sp.equip_no = te.equip_no
and tem.equipmodal_no = te.equipmodal_no
and tet.equiptype_no = tem.equiptype_no
and (select distinct spt.serv_id
from serv_product spt,
product p,
product_pack_plan pp,
product_rent_plan pr
where p.pack_plan_id = pp.pack_plan_id
and spt.serv_id = sp.serv_id
and spt.cust_id = sp.cust_id
and spt.product_id = p.product_id
and pp.price_plan_id = pr.price_plan_id
and p.basic_flag = '1'
and pr.rate = 0) is not null --基本包不是免费的
and sp.product_state in ('1', '0')
and sp.equiptype_no in ('1', '2')
and tet.equiptype_no in ('1', '2')
and c.state = '70A'
and s.serial_nbr = '2'
and s.state in ('F0A', 'F0P')
and sp.buy_type = '2'
and sp.cust_id in (select distinct sp2.cust_id
from serv_product sp2,
serv s2,
product p2,
cust c2,
tem_equipment te2,
tem_equip_modal tem2,
tem_equip_type tet2
where s2.serv_id = sp2.serv_id
and p2.product_id = sp2.product_id
and s2.cust_id = c2.cust_id
and sp2.equip_no = te2.equip_no
and tem2.equipmodal_no = te2.equipmodal_no
and tet2.equiptype_no = tem2.equiptype_no
and (select distinct spt2.serv_id
from serv_product spt2,
product p2,
product_pack_plan pp2,
product_rent_plan pr2
where spt2.serv_id = sp2.serv_id
and spt2.cust_id = sp2.cust_id
and p2.pack_plan_id = pp2.pack_plan_id
and spt2.product_id = p2.product_id
and pp2.price_plan_id = pr2.price_plan_id
and p2.basic_flag = '1'
and pr2.rate <> 0) is not null
and sp2.product_state in ('1', '0')
and sp2.equiptype_no in ('1', '2')
and tet2.equiptype_no in ('1', '2')
and c2.state = '70A'
and s2.serial_nbr = '1'
and s2.state in ('F0A', 'F0P')
and sp2.buy_type = '1') -- 赠送的基本包都是免费的
1。能过滤掉最多数据的条件放在where语句的最后。
2。in 可以用exists代替，或者直接写 (col＝'A' or col='B' )
3。 (select distinct spt2.serv_id
from serv_product spt2,
product p2,
product_pack_plan pp2,
product_rent_plan pr2
where spt2.serv_id = sp2.serv_id
and spt2.cust_id = sp2.cust_id
and p2.pack_plan_id = pp2.pack_plan_id
and spt2.product_id = p2.product_id
and pp2.price_plan_id = pr2.price_plan_id
and p2.basic_flag = '1'
and pr2.rate <> 0) is not null
这段好像用exists代替，用rownum只查一个出来，distinct也没用。。不知道说的对不对，请高人指点。。
select distinct sp.serv_product_id servProductId,
sp.equip_no equipNo,
sp.serv_id servId,
s.serial_nbr serialNbr,
sp.cust_id custId,
c.cust_no custNo,
sp.product_id productId,
to_char(sp.created_date, 'YYYY-MM-DD HH24:MI:SS') createdDate
from serv_product sp,
serv s,
product p,
cust c,
tem_equipment te,
tem_equip_modal tem,
tem_equip_type tet
where s.serv_id = sp.serv_id
and p.product_id = sp.product_id
and s.cust_id = c.cust_id
and sp.equip_no = te.equip_no
and tem.equipmodal_no = te.equipmodal_no
and tet.equiptype_no = tem.equiptype_no
and not exists (select 1
from serv_product spt,
product p,
product_pack_plan pp,
product_rent_plan pr
where p.pack_plan_id = pp.pack_plan_id
and spt.serv_id = sp.serv_id
and spt.cust_id = sp.cust_id
and spt.product_id = p.product_id
and pp.price_plan_id = pr.price_plan_id
and p.basic_flag = '1'
and pr.rate = 0) --基本包不是免费的
and sp.product_state in ('1', '0')
and sp.equiptype_no in ('1', '2')
and tet.equiptype_no in ('1', '2')
and c.state = '70A'
and s.serial_nbr = '2'
and s.state in ('F0A', 'F0P')
and sp.buy_type = '2'
and exists (select 1
from serv_product sp2,
serv s2,
product p2,
cust c2,
tem_equipment te2,
tem_equip_modal tem2,
tem_equip_type tet2
where s2.serv_id = sp2.serv_id
and p2.product_id = sp2.product_id
and s2.cust_id = c2.cust_id
and sp2.equip_no = te2.equip_no
and tem2.equipmodal_no = te2.equipmodal_no
and tet2.equiptype_no = tem2.equiptype_no
and sp2.cust_id = sp.cust_id
and not exists (select 1
from serv_product spt2,
product p2,
product_pack_plan pp2,
product_rent_plan pr2
where spt2.serv_id = sp2.serv_id
and spt2.cust_id = sp2.cust_id
and p2.pack_plan_id = pp2.pack_plan_id
and spt2.product_id = p2.product_id
and pp2.price_plan_id = pr2.price_plan_id
and p2.basic_flag = '1'
and pr2.rate <> 0)
and sp2.product_state in ('1', '0')
and sp2.equiptype_no in ('1', '2')
and tet2.equiptype_no in ('1', '2')
and c2.state = '70A'
and s2.serial_nbr = '1'
and s2.state in ('F0A', 'F0P')
and sp2.buy_type = '1') -- 赠送的基本包都是免费的
给分吧 嘿嘿
sp.equip_no equipNo,
sp.serv_id servId,
s.serial_nbr serialNbr,
sp.cust_id custId,
c.cust_no custNo,
sp.product_id productId,
to_char(sp.created_date, 'YYYY-MM-DD HH24:MI:SS') createdDate
from serv_product sp,
serv s,
product p,
cust c,
tem_equipment te,
tem_equip_modal tem,
tem_equip_type tet
where s.serv_id = sp.serv_id
and p.product_id = sp.product_id
and s.cust_id = c.cust_id
and sp.equip_no = te.equip_no
and tem.equipmodal_no = te.equipmodal_no
and tet.equiptype_no = tem.equiptype_no
and exists (select rowid
from product_pack_plan pp,
product_rent_plan pr
where p.pack_plan_id = pp.pack_plan_id
and pp.price_plan_id = pr.price_plan_id
and p.basic_flag = '1'
and pr.rate = 0) --基本包不是免费的
and sp.product_state in ('1', '0')
and sp.equiptype_no in ('1', '2')
and tet.equiptype_no in ('1', '2')
and c.state = '70A'
and s.serial_nbr = '2'
and s.state in ('F0A', 'F0P')
and sp.buy_type = '2'
and exists (select rowid
from serv_product sp2,
serv s2,
product p2
where s2.serv_id = sp2.serv_id
and p2.product_id = sp2.product_id
and exists (select rowid
from product_pack_plan pp2,
product_rent_plan pr2
where p2.pack_plan_id = pp2.pack_plan_id
and pp2.price_plan_id = pr2.price_plan_id
and p2.basic_flag = '1'
and pr2.rate <> 0)
and s2.serial_nbr = '1'
and sp2.buy_type = '1'
and sp.cust_id = sp2.cust_id)