查询匹配度

产品智能推介。找匹配度最高的记录。

 

select *  

from(select id,org_id,sum(  

(case when instr(product_type,'2')>0 then 1 else 0 end)  

+(case when interest>5 and interest<11 then 1 else 0 end)  

+(case when 2000 between amount_low and amount_high then 1 else 0 end)  

)  

as cnt from hideseek.OrgProduct group by id) as temp  

where cnt>0  

order by cnt desc,id asc  

 

 

 

//按照产品名称分组，每组取匹配度最高的记录出来

select * from (

select 

    *

from

    (select 

        p . *,

            logo.org_logo,

            sum((case

                when instr(product_type, '1') > 0 then 1

                else 0

            end) + (case

                when interest between 1 and 1 then 1

                else 0

            end) + (case

                when instalment between 2 and 12 then 1

                else 0

            end) + (case

                when mobile <= 0 then 1

                else 0

            end) + (case

                when e_business <= 0 then 1

                else 0

            end) + (case

                when housing_fund <= 0 then 1

                else 0

            end) + (case

                when social_insurance <= 0 then 1

                else 0

            end)) as matchingDegree

    from

        tradeplatform.OrgProduct p

    left join tradeplatform.OrgLogo logo ON p.id = logo.pro_id

    where

        p.plat_status = 1 and product_type = '1'

    group by id) as temp

where

    matchingDegree > 0

        and org_id not in ('juxinli')

 

order by matchingDegree desc 

) A GROUP BY product_name ORDER BY matchingDegree DESC