字符串形态的表达式是不会被运算的 mysql 并没有类似 eval 的函数,或许你可以自己写一个对于你的这个例子,可以写作 SELECT substring_index(a,'*',1) * substring_index(substring_index(a,'*',2),'*',-1) * substring_index(substring_index(a,'*',3),'*',-1) from (select REPLACE('500x1x10','x','*') AS a) t 但不具备普遍意义
如果mysql字段存储500x1x10字符串,但是计算的时候我需要将x替换成*,才可以进行计算。
可以查询出来了用php计算就简单多了
SELECT process.fid,SUM(CASE WHEN (product.box_product_dimensions/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS sumweight, (CASE WHEN (product.box_product_dimensions/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS weight FROM `process` LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY fid这是我sql当中其中一段sql,贴出来给你看下。如果计算拿到程序里面,就不是很方便。
SELECT process.sku,process.fid,SUM(CASE WHEN (REPLACE(product.box_product_dimensions,'x','*')/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS sumweight, (CASE WHEN (REPLACE(product.box_product_dimensions,'x','*')/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS weight FROM `process` LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY fidproduct.box_product_dimensions 存储的是字符串,形如:14x12x36
SELECT process.sku,process.comment2, SUM(CASE WHEN (SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000) >box_shipping_weight THEN (SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000) ELSE box_shipping_weight END ) AS sumweight, (CASE WHEN (SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000) >box_shipping_weight THEN (SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000) ELSE box_shipping_weight END ) AS weight FROM `process` LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY comment2
mysql 并没有类似 eval 的函数,或许你可以自己写一个对于你的这个例子,可以写作
SELECT substring_index(a,'*',1) * substring_index(substring_index(a,'*',2),'*',-1) * substring_index(substring_index(a,'*',3),'*',-1)
from (select REPLACE('500x1x10','x','*') AS a) t
但不具备普遍意义
SELECT process.fid,SUM(CASE WHEN (product.box_product_dimensions/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS sumweight,
(CASE WHEN (product.box_product_dimensions/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS weight FROM `process`
LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY fid这是我sql当中其中一段sql,贴出来给你看下。如果计算拿到程序里面,就不是很方便。
SELECT process.sku,process.fid,SUM(CASE WHEN (REPLACE(product.box_product_dimensions,'x','*')/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS sumweight,
(CASE WHEN (REPLACE(product.box_product_dimensions,'x','*')/5000)>box_shipping_weight THEN product.box_product_dimensions/5000 ELSE box_shipping_weight END ) AS weight FROM `process`
LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY fidproduct.box_product_dimensions 存储的是字符串,形如:14x12x36
SUM(CASE WHEN
(SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000)
>box_shipping_weight
THEN
(SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000)
ELSE box_shipping_weight
END
) AS sumweight,
(CASE WHEN
(SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000)
>box_shipping_weight THEN
(SELECT (SELECT SUBSTRING_INDEX(box_product_dimensions,'x',1))*(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(box_product_dimensions,'x',2),'x',-1))*(SELECT SUBSTRING_INDEX(box_product_dimensions,'x',-1))/5000)
ELSE box_shipping_weight END ) AS weight FROM `process`
LEFT JOIN product ON product.pid=process.pid WHERE 1 AND process.receiver_id>0 AND statu="3" AND property="转仓单" AND isover="N" GROUP BY comment2
为什么要 SELECT ??