数据库字段值:日单量:100 | 出厂价格:5000.0 | 销售价:57000.34 | 优惠价:200.00
SELECT
CONCAT('日单量: ',sum(
substring(a.order_msg,
instr(a.order_msg,'日单量') + 4,
instr(a.order_msg, ' | ') - 4
)
),
" | 出厂价格: ",sum(
substr(
a.order_msg,
instr(a.order_msg, '出厂价格') + 5,
instr(a.order_msg, '总')-(instr(a.order_msg, '出厂价格') + 7)
)
),' | 销售价: ',sum(
substr(
a.order_msg,
instr(a.order_msg, '销售价') + 4,
instr(a.order_msg, '优惠价')-(instr(a.order_msg, '销售价') + 7)
)
),' | 优惠价: ',sum(
substring(
a.order_msg,
instr(
a.order_msg,
'优惠价'
) + 4,
LENGTH(a.order_msg)
)
)) as ptCasinoMsg
FROM
sale_order a;
提取的结果值:100 | 5000.0 | 57000.34 | 200.00
SELECT
CONCAT('日单量: ',sum(
substring(a.order_msg,
instr(a.order_msg,'日单量') + 4,
instr(a.order_msg, ' | ') - 4
)
),
" | 出厂价格: ",sum(
substr(
a.order_msg,
instr(a.order_msg, '出厂价格') + 5,
instr(a.order_msg, '总')-(instr(a.order_msg, '出厂价格') + 7)
)
),' | 销售价: ',sum(
substr(
a.order_msg,
instr(a.order_msg, '销售价') + 4,
instr(a.order_msg, '优惠价')-(instr(a.order_msg, '销售价') + 7)
)
),' | 优惠价: ',sum(
substring(
a.order_msg,
instr(
a.order_msg,
'优惠价'
) + 4,
LENGTH(a.order_msg)
)
)) as ptCasinoMsg
FROM
sale_order a;
提取的结果值:100 | 5000.0 | 57000.34 | 200.00