MySQL 替换字符串 SUM 然后总计

Question

希望从替换字段的总和中获取每个 ID 的总计。

SELECT
    insurance_carrier as ID, SUM(REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')) AS es_reserve,
    SUM(REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))AS structure_reserve,
    SUM(es_reserve+structure_reserve) AS total
FROM job_tbl
WHERE
    job_status NOT IN(2,4,6,7,9) AND
    insurance_carrier !=0 AND      
    FROM_UNIXTIME(date_of_loss,'%m') = MONTH(NOW()) AND
    FROM_UNIXTIME(date_of_loss,'%Y') = YEAR(NOW())
GROUP BY insurance_carrier

我从es_reserve和structure_reserve中得到结果，但总数为0。

顺便说一句，这些字段包含诸如 $2,300 之类的项目 - 这就是替换的原因

任何帮助将不胜感激!!!

编辑:这是产生的结果

Array
(
[ID] => 14
[es_reserve] => 5000
[structure_reserve] => 35000
)

Array
(
[ID] => 15
[es_reserve] => 2500
[structure_reserve] => 2500
)

Array
(
[ID] => 41
[es_reserve] => 2500
[structure_reserve] => 2500
)

Array
(
[ID] => 44
[es_reserve] => 2500
[structure_reserve] => 
)

这是我希望它产生的内容

Array
(
[ID] => 14
[es_reserve] => 5000
[structure_reserve] => 35000
[total] => 40000
)

Array
(
[ID] => 15
[es_reserve] => 2500
[structure_reserve] => 2500
[total] => 5000
)

Array
(
[ID] => 41
[es_reserve] => 2500
[structure_reserve] => 2500
[total] => 5000
)

Array
(
[ID] => 44
[es_reserve] => 2500
[structure_reserve] => 
[total] => 2500
)

Answer 1

总计列正在对原始列值进行求和，在选择中定义的别名不会在同一选择中使用。您可以在进行总列计算时重复替换语句

SUM(  REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','') 
      + REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))
Total, 

而不是

SUM(es_reserve+structure_reserve) as total 

查询变成，按照评论中的要求进行排序。

SELECT insurance_carrier as ID, SUM(REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')) AS es_reserve, SUM(REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))AS structure_reserve, SUM( REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','') + REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-','')) as Total FROM job_tbl WHERE job_status NOT IN(2,4,6,7,9) AND insurance_carrier !=0 AND FROM_UNIXTIME(date_of_loss,'%m') = MONTH(NOW()) AND FROM_UNIXTIME(date_of_loss,'%Y') = YEAR(NOW()) GROUP BY insurance_carrier
order by SUM( REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','') + REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-','')) desc

或者将其用作子查询

SELECT T.*,     SUM(es_reserve+structure_reserve) AS total
FROM 
(

SELECT
    insurance_carrier as ID, SUM(REPLACE(REPLACE(REPLACE(es_reserve,'$',''),',',''),'-','')) AS es_reserve,
    SUM(REPLACE(REPLACE(REPLACE(structure_reserve,'$',''),',',''),'-',''))AS structure_reserve

FROM job_tbl
WHERE
    job_status NOT IN(2,4,6,7,9) AND
    insurance_carrier !=0 AND      
    FROM_UNIXTIME(date_of_loss,'%m') = MONTH(NOW()) AND
    FROM_UNIXTIME(date_of_loss,'%Y') = YEAR(NOW())
GROUP BY insurance_carrier
) T