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,'-','%','］%')

近一步优化一下需要的查询语句：

SELECT DISTINCT c.city_id,c.area_id, 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 
where al.address LIKE CONCAT('［',c.area_name,'-','%','］%');

筛选不重复的数据

DISTINCT需要放在查询字段的前面才行

用where 还是用having

前面一条查询里面用了having 但是发现查询字段中必须存在having里面的字段才行，遂改用where了，（原先是担心复杂操作，在where里面不一定能执行）

再优化一下

SELECT DISTINCT c.city_name,c.area_name,c.city_id,c.area_id, 
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 
where al.address LIKE CONCAT('［',c.area_name,'-','%','］%') having c.area_name <> region;

还是用到了having 哈哈