select a.employeeid,
(select listagg(g.Email, ';') within group(order by e.CustomID)
from LCInsured e, LIInsContact f, LIBranchEmailSet g
where e.CustomID = f.CustomID
and e.EmployeeID = f.EmployeeID
and f.ContactNo = g.ContactNo
and f.Relation = '01'
and g.IsValid = '01'
AND g.emailstatus = '01'
and e.Relationship = '01'
--A and e.employeeid = a.employeeid
--A and e.grpcontno = a.grpcontno
--B and e.insuredid = (select insuredid from lcinsured where grpcontno = a.grpcontno and employeeid = a.employeeid and relationship='01')
)
FROM (select *
from (select a.*,
ROW_NUMBER() OVER(partition by a.customid order by a.expirydate desc) rn
from lcinsured a
where 1 = 1
and a.effectivedate <= a.expirydate
and a.grpcontno = 'MSH202006157963')
where rn = 1) a
left join LCGrpContRenewMiddleinsured b
on b.customid = a.customid
and b.employeeid = a.employeeid
join lcgrpplan c
on a.grpcontno = c.grpcontno
and a.grpplancode = c.grpplancode
order by a.insuredid
我不知道为什么. 反正结果是
A 11秒
B 1秒
虽然优化了 但不知道原因. 这个sql是取被保人的主被保人的邮箱地址.