個人原創的一段sql代碼

select

tb1.phone,

tb1.pin,

tb2.starttype,

tb2.name1,

tb1.prodname,

tb1.state,

tb1.city,

tb1.area,

tb1.address

from

(select

pt1.phone,

pt1.pin,

pt1.prodname,

pt1.state,

pt1.city,

pt1.area,

pt1.address

from

(select

distinct

aa.phone,

pt.pin,

pt.prodname,

pt.state,

pt.city,

pt.area,

pt.address,

row_number()over(partition by pt.pin ordr by pt.senddate desc) rm

from

(

select

a.contactid phone,

a.campaign,

c.pin,

c.contactid

from afxa_lin a

left join tb_contact c on a.contactid=c.mobile

ordr by to_number(a.campaign))aa

left join tb_putdata1 pt on aa.pin=pt.pin

where pt.producttype in (01,08)

) pt1 where rm=1 ) tb1,

(select

aa.phone,

aa.pin,

aa.starttype,

(

select od.prodname

from tb_orderdet od

left join tb_orderhist oh on od.orderviewid=oh.orderview

where aa.orderviewid=oh.orderviewid and aa.viewstartdt=oh.prodid and od.catid in (01,08) and oh.monthnumber=1

) name1

from

(

select

distinct

a.contactid phone,

c.pin,

ov.starttpye,

ov.orderstartdt,

ov.orderviewid,

row_number()over(partition by ov.contactid ordr by ov.crdt desc) rn

from afxa_lin

left join tb_contact c on a.contactid=c.mobile

left join tb_orderview ov on c.contactid =ov.contactid

) aa where rn=1) tb2

where tb1.phone=tb2.phone

推薦閱讀：