MySQL字符串如果需要将某个字段里的内容进行分割并写入到其他字段中,
有电话号码如下: 010-88888882-5612,将电话号码划分为总机和分机号:
SELECT
SUBSTRING_INDEX(phone,'-',2) AS PNumber,
SUBSTRING_INDEX(phone,'-', -1) AS Ext,
phone FROM tb_user
WHERE ucid=271338;
+--------------+------+-------------------+
| PNumber | Ext | phone |
+--------------+------+-------------------+
| 010-88888882 | 5612 | 010-88888882-5612 |
+--------------+------+-------------------+
变更分机号码为1234:
UPDATE tb_user SET
phone=CPNCAT( SUBSTRING_INDEX( phone,'-',2 ), '-', '1234' )
WHERE ucid = 271338;
按省份统计订单 path_text: 中国,广西壮族自治区,柳州市
SELECT COUNT(DISTINCT o.id) value ,SUBSTRING_INDEX(SUBSTRING_INDEX(p.path_text,',',2),',',-1) name
FROM logistics_location ll
LEFT JOIN logistics_order o on ll.logistic_order_id= o.id
LEFT JOIN sxb_quote s ON o.sxb_quote_id =s.id
LEFT JOIN province_city p on s.end_city_id=p.id
WHERE (o.status=4 or o.status=5) AND o.is_false_delete=0 and ll.sign=4
GROUP BY name