select DISTINCT companyId, companyCode, companyName, -- 相同字段去重,从相同字段取出最小显示一行 businessType ,min(display) display from ( (select s.company_id companyId, s.company_code companyCode, s.company_name companyName, s.business_type businessType , '1' display --用自定义字段区分显示顺序 from nclm_servicer_info s -- 省 市 类别相同优先排列 join NCLM_SERVICER_RELATION nsrp on nsrp.COMPANY_ID=S.company_id AND nsrp.RELATION_TYPE='2' and nsrp.VALID_IND='1' AND nsrp.RELATION_TYPE_ID=#{servicerInfoQueryVo.businessAreaProvince} join NCLM_SERVICER_RELATION nsrc on nsrc.COMPANY_ID=S.company_id AND nsrc.RELATION_TYPE='3' and nsrc.VALID_IND='1' AND nsrc.RELATION_TYPE_ID=#{servicerInfoQueryVo.businessAreaCity} join NCLM_SERVICER_RELATION nsrr on nsrr.COMPANY_ID=S.company_id AND nsrr.RELATION_TYPE='4' and nsrr.VALID_IND='1' AND nsrr.RELATION_TYPE_ID=#{servicerInfoQueryVo.riskClass} where s.company_type = '2' and s.valid_ind= '1') union (select s.company_id companyId, s.company_code companyCode, s.company_name companyName, s.business_type businessType , '2' display from nclm_servicer_info s -- 省 市 相同优先排第二 join NCLM_SERVICER_RELATION nsrp on nsrp.COMPANY_ID=S.company_id AND nsrp.RELATION_TYPE='2' and nsrp.VALID_IND='1' AND nsrp.RELATION_TYPE_ID=#{servicerInfoQueryVo.businessAreaProvince} join NCLM_SERVICER_RELATION nsrc on nsrc.COMPANY_ID=S.company_id AND nsrc.RELATION_TYPE='3' and nsrc.VALID_IND='1' AND nsrc.RELATION_TYPE_ID=#{servicerInfoQueryVo.businessAreaCity} where s.company_type = '2' and s.valid_ind= '1') union (select s.company_id companyId, s.company_code companyCode, s.company_name companyName, s.business_type businessType , '3' display from nclm_servicer_info s -- 类别相同排第三 join NCLM_SERVICER_RELATION nsrr on nsrr.COMPANY_ID=S.company_id AND nsrr.RELATION_TYPE='4' and nsrr.VALID_IND='1' AND nsrr.RELATION_TYPE_ID=#{servicerInfoQueryVo.riskClass} where s.company_type = '2' and s.valid_ind= '1') union (select s.company_id companyId, s.company_code companyCode, s.company_name companyName, s.business_type businessType , '4' display -- 其他排列在最后 from nclm_servicer_info s where s.company_type = '2' and s.valid_ind= '1') ) tw where 1=1 -- 加入筛选条件 group by companyId, companyCode, companyName, businessType order by display --根据相同字段分组 ,显示顺序字段排序