--需求: 根据企业名称,企业三码(社会信用代码,社会组织代码,注册号),在企业表查找企业.
如果有多个参数匹配到不同企业名称,比如,企业名称匹配到A公司,社会信用代码匹配到B公司.
这种情况优先匹配顺序是,企业名称->社会信用代码->社会组织机构代码->注册号.
下面是sql实现:
select
CASE
<if test="companyName != null and companyName !='' ">
WHEN a.company_full_name = #{companyName,jdbcType=VARCHAR} THEN 1
</if>
<if test="creditCode != null and creditCode !='' ">
WHEN a.unite_code = #{creditCode,jdbcType=VARCHAR} THEN 2
</if>
<if test="orgCode != null and orgCode !='' ">
WHEN a.organization_code = #{orgCode,jdbcType=VARCHAR} THEN 3
</if>
<if test="regNo != null and regNo !='' ">
WHEN a.trade_register_code = #{regNo,jdbcType=VARCHAR} THEN 4
</if>
WHEN 1=1 THEN 5
ELSE 5 END as i,
a.company_id as "companyId",
a.company_full_name as "companyName"
from ebd_coding_company_name a
where 1=2
<if test="regNo != null and regNo !='' ">
or a.trade_register_code = #{regNo,jdbcType=VARCHAR}
</if>
<if test="orgCode != null and orgCode !='' ">
or a.organization_code = #{orgCode,jdbcType=VARCHAR}
</if>
<if test="creditCode != null and creditCode !='' ">
or a.unite_code = #{creditCode,jdbcType=VARCHAR}
</if>
<if test="companyName != null and companyName !='' ">
or a.company_full_name = #{companyName,jdbcType=VARCHAR}
</if>
order by i limit 1
如果有更好的见解,欢迎大神留言沟通.