select
@str:='100-400万',@maxTypePrice:='500',@minTypePrice:='300',
@price_scope:=if(locate('-',@str) > 0,replace(@str,'万',''),
if(locate('万以下',@str) > 0,replace(@str,'万以下',''),replace(@str,'万以上',''))),
@type:=if(locate('-',@str) > 0,1,if(locate('万以下',@str) > 0,2,3)),
@min:=if(@type=1,SUBSTRING_INDEX(@price_scope,'-',1),@price_scope),
@max:=if(@type=1,SUBSTRING_INDEX(@price_scope,'-',-1),@price_scope),
ELT(
@type,
if(@min <= @maxTypePrice and @max >= @minTypePrice,1,0),
if(@price_scope <= @maxTypePrice and @price_scope >= @minTypePrice,1,0),
if(@price_scope <= @maxTypePrice and @price_scope >= @minTypePrice,1,0)
) as weight;
匹配原则:交集
购房匹配度 weight,1 代表匹配,0 代表不匹配