SQL高手帮忙优化一下

[Jay2018509]

update LCGrpCont set Peoples2 = (
select count(distinct InsuredNo)
from LCPol
where GrpContNo = '00025046000001'
and PolTypeFlag = '0'
and AppFlag = '1') , OnWorkPeoples = (
select count(*)
from LCInsured a, LCCont b
where a.ContNo = b.ContNo
and a.GrpContNo = '00025046000001'
and a.InsuredStat = '1'
and b.AppFlag = '1') , OffWorkPeoples = (
select count(*)
from LCInsured a, LCCont b
where a.ContNo = b.ContNo
and a.GrpContNo = '00025046000001'
and a.InsuredStat = '2'
and b.AppFlag = '1') , OtherPeoples = (
select count(distinct InsuredNo)
from LCPol
where GrpContNo = '00025046000001'
and PolTypeFlag = '0'
and AppFlag = '1') -(
select count(*)
from LCInsured a, LCCont b
where a.ContNo = b.ContNo
and a.GrpContNo = '00025046000001'
and a.InsuredStat = '1'
and b.AppFlag = '1') -(
select count(*)
from LCInsured a, LCCont b
where a.ContNo = b.ContNo
and a.GrpContNo = '00025046000001'
and a.InsuredStat = '2'
and b.AppFlag = '1')
where GrpContNo = '00025046000001'

[--小F--]

把conut(*)换成count(1)

[ks_reny]

子查詢太多了,用連接吧.

[JonasFeng]

建议楼主
把这两个表 LCInsured，LCCont写一个视图。

还有能用JOIN 的就不用子查询

[-晴天]

update LCGrpCont set 

Peoples2 = ( 
  select count(distinct InsuredNo) 
    from LCPol 
    where GrpContNo = '00025046000001' 
      and PolTypeFlag = '0' 
      and AppFlag = '1') , 

OnWorkPeoples = ( 
  select count(*) 
    from LCInsured a, LCCont b 
    where a.ContNo = b.ContNo 
      and a.GrpContNo = '00025046000001' 
      and a.InsuredStat = '1' 
      and b.AppFlag = '1') , 

OffWorkPeoples = ( 
  select count(*) 
    from LCInsured a, LCCont b 
    where a.ContNo = b.ContNo 
      and a.GrpContNo = '00025046000001' 
      and a.InsuredStat = '2' 
      and b.AppFlag = '1')

 where GrpContNo = '00025046000001'
go

update LCGrpCont set OtherPeoples=
	Peoples2-OnWorkPeoples-OffWorkPeoples
where GrpContNo = '00025046000001'
go

[中国风]

update LCGC
set Peoples2=LCP.InsuredNo,OnWorkPeoples=LCI.OnWorkPeoples,OffWorkPeoples=LCI.OffWorkPeoples,
OtherPeoples=LCP.InsuredNo-LCI.OtherPeoples
from LCGrpCont LCGC
,(select count(distinct InsuredNo )InsuredNo from LCPol where PolTypeFlag = '0'  and AppFlag = '1' and GrpContNo = '00025046000001' ) LCP,
( select sum(case when a.InsuredStat = '1'   then 1 else 0 end) as OnWorkPeoples ,sum(case when a.InsuredStat = '2'  then 1 else 0 end) as OffWorkPeoples,count(*) as OtherPeoples from LCInsured a, LCCont b     where a.ContNo = b.ContNo       and a.GrpContNo = '00025046000001'  and b.AppFlag = '1') LCI
where LCGC.GrpContNo = '00025046000001' 

[ai_li7758521]

eclare @P1 int,@P2 int,@p3 int

select @P1=count(distinct InsuredNo) 
from LCPol 
where GrpContNo = '00025046000001' 
  and PolTypeFlag = '0' 
  and AppFlag = '1'

select @P2=sum(case a.InsuredStat when 1 then 1 else 0 end), 
	   @P3=sum(case a.InsuredStat when 2 then 1 else 0 end)
from LCInsured a, LCCont b 
where a.ContNo = b.ContNo 
  and a.GrpContNo = '00025046000001' 
  and a.InsuredStat = '1' 
  and b.AppFlag = '1'
	

update LCGrpCont 
set Peoples2 = @P1
   ,OnWorkPeoples =@p2
   ,OffWorkPeoples =@p3
   ,OtherPeoples = @P1-@p2-@p3