单字段拆分查询使用
用法
SUBSTRING_INDEX(val,'|',1) as va1,
SUBSTRING_INDEX(val,'|',-1) as va2
示例1
SELECT * from (SELECT id,SUBSTRING_INDEX(val,'|',1) as va1,
SUBSTRING_INDEX(val,'|',-1) as va2 from `value` )a where id=1
示例2
SELECT * from (SELECT id,SUBSTRING_INDEX(val,'|',1) as t1,
SUBSTRING_INDEX(SUBSTRING_INDEX(val,'|',2),'|',-1) as t2,
SUBSTRING_INDEX(SUBSTRING_INDEX(val,'|',3),'|',-1) as t3,
SUBSTRING_INDEX(val,'|',-1) as t4 from `value` )a where id=2;
示例3
SELECT
*,
va1 '营业开始时间',
va2 '营业结束时间'
FROM
(
SELECT * ,SUBSTRING_INDEX(business_time,'-',1) as va1,SUBSTRING_INDEX(business_time,'-',-1) as va2 FROM table
) emp