mysql的截取与拼接_mysql数据截取，拼接

案例

SELECT *,SUBSTRING(al.address,INSTR(al.address,'-')+1,INSTR(al.address,'］')-INSTR(al.address,'-')-1) region FROM apartment_list al

LEFT JOIN

(SELECT c2.pid city_id,c2.id area_id,c1.city_name,c2.city_name area_name FROM citys c1 INNER JOIN citys c2 ON c1.id = c2.pid) c

ON c.city_id = al.city_id AND c.area_id = al.area_id

HAVING al.address LIKE CONCAT('［',c.area_name,'-','%','］%') LIMIT 0,10;

通过城市表关联查找区域表中的商圈字段(比区小的区域范围)

image.png

数据截取

SUBSTRING(al.address,INSTR(al.address,'-')+1,INSTR(al.address,'］')-INSTR(al.address,'-')-1)

数据拼接

CONCAT('［',c.area_name,'-','%','］%')

近一步优化一下需要的查询