大侠们,这样的SQL能不能优化呀? 表bc_team中的数据是父子关系,现在在页面是一个树,当点击树的任何一级,要求把该级下面的所有子级和子级的子级的个数统计出来,也就是SQL中的num1,因为不知道有多少级,所以用了start with b.bct_parentid=a.bct_id
connect by prior b.bct_id=b.bct_parentid 这样的语句.num2,num3 又是从bc_managerinfo 表中统计该team下的员工数,同样包括点击这一级下的子级,子子级等,我想SQL主要不好的地方是num2和num3的这两个数据,唯一不同的是一个要求统计bcmstatus in ('S03','S06') ,另一个要求统计bcmstatus not in ('S03','S06') ,所以这里循环了两次,降低了效率,请大侠们帮帮忙,谢谢!
select a.cityid,a.bct_name, a.bct_desc ,
(select count(*) from bc_team b start with b.bct_parentid=a.bct_id
connect by prior b.bct_id=b.bct_parentid) num1 ,
(select count(*) from bc_managerinfo g
where g.bcmstatus in ('S03','S06')
and g.bct_id in (select d.bct_id from bc_team d start with d.bct_parentid=a.bct_id
connect by prior d.bct_id=d.bct_parentid
union select a.bct_id bct_id from bc_team)) num2,
(select count(*) from bc_managerinfo e
where e.bcmstatus not in ('S03','S06')
and e.bct_id in (select d.bct_id from bc_team d start with
d.bct_parentid=a.bct_id
connect by prior d.bct_id=d.bct_parentid
union select a.bct_id bct_id from bc_team)) num3 from bc_team a
where a.bct_parentid =?
connect by prior b.bct_id=b.bct_parentid 这样的语句.num2,num3 又是从bc_managerinfo 表中统计该team下的员工数,同样包括点击这一级下的子级,子子级等,我想SQL主要不好的地方是num2和num3的这两个数据,唯一不同的是一个要求统计bcmstatus in ('S03','S06') ,另一个要求统计bcmstatus not in ('S03','S06') ,所以这里循环了两次,降低了效率,请大侠们帮帮忙,谢谢!
select a.cityid,a.bct_name, a.bct_desc ,
(select count(*) from bc_team b start with b.bct_parentid=a.bct_id
connect by prior b.bct_id=b.bct_parentid) num1 ,
(select count(*) from bc_managerinfo g
where g.bcmstatus in ('S03','S06')
and g.bct_id in (select d.bct_id from bc_team d start with d.bct_parentid=a.bct_id
connect by prior d.bct_id=d.bct_parentid
union select a.bct_id bct_id from bc_team)) num2,
(select count(*) from bc_managerinfo e
where e.bcmstatus not in ('S03','S06')
and e.bct_id in (select d.bct_id from bc_team d start with
d.bct_parentid=a.bct_id
connect by prior d.bct_id=d.bct_parentid
union select a.bct_id bct_id from bc_team)) num3 from bc_team a
where a.bct_parentid =?
改为:where e.bcmstatus = 'S03' or bcmstatus ='S06'
where e.bcmstatus not in ('S03','S06')
改为: where e.bcmstatus != 'S03' and bcmstatus !='S06'注意用in的时候尽量用exists 或者别的代替,因为in 或者 not in 的效率比较差