sql如下:
SELECT n.polno 保单号,
(SELECT np.uppercomcname
FROM ncdcompanycollation np
where np.comcode = n.managecomcode
and np.uppercomlevel = 'L2') 二级机构名称,
n.agentNo 保单服务人员代码,
n.agentName 保单服务人员,
n.managecomcode 归属机构代码,
n.managecom 归属机构名称,
n.riskname 险种名称,
n.riskchannel 销售渠道,
(select codecname
FROM ncdcode
where codetype = 'saleTypeCode'
and codecode = n.saleTypeCode) as 销售方式,
n.appntname 投保人姓名,
n.appntsex 性别,
(case
when n.appntmobiltel is not null then
n.appntmobiltel
else
n.appnthometel
end) 手机号码
, (case
when n.appntmobiltel is not null then
n.appntmobiltel
else
n.appnthometel
end) 联系电话,
n.agentphone 收费电话,
n.issuedate 承保日期,
(case
when nc.dailstatus = '1' then
'放弃'
when nc.dailstatus = '2' then
'成功'
else
'未成功'
end) 回访结果,
cp.phonenumber 回访成功电话,
(case
when instr(cp.phonenumber, n.appntmobiltel) > 0 or instr(cp.phonenumber, n.appnthometel) > 0 then
'是'
else
'否'
end) 回访号是否手机号一致
From flextest t
left join ncocust n on t.b = n.polno
left join NcOAppInfo af
on n.appntno = af.appno
left join NcOCustPhone cp
on n.appntno = cp.appntcustno
left join nconewcallcust nc
on n.polno = nc.polno
order by n.polno asc
查询结果:如下图: 查询结果存在保单号重复数据,尚未完成去重,后续完成