如何用objects.ID替换SQL固定值14，按ID计算对应totalcost

数据库表结构 #

• objects表：

  字段名	说明
  ID (pk)	主键（原标注红色）
  is_rel_strg

• strg_catgs表：CatID可与多个elemID重复关联

  字段名	说明
  catID	主键（原标注红色）
  elemID	关联字段（原标注金色）
  Disrate

• strg_op表：

  字段名	说明
  elemID	关联字段（原标注金色）
  opvalue
  opcost

• strg表：

  字段名	说明
  elemID (pk)	主键（原标注金色）
  name

原查询问题 #

现有SQL查询中使用固定值14关联strg_catgs.catID，导致所有符合条件的objects.ID对应的totalcost值完全相同：

SELECT SUM(t.totalcost) AS totalcost ,objects.ID  
FROM (
    select sum(strg_op.opcost) / sum(strg_op.opvalue * 1000) * strg_catgs.Disrate as totalcost    
    from strg_catgs     
    inner join strg_op  on strg_op.ElemID = strg_catgs.elemID         
        and strg_catgs.elemID = (            
            SELECT strg_catgs.elemID            
            WHERE strg_catgs.catID =14 )         
        and strg_catgs.catID = 14 and strg_catgs.descr = "cwe"    
    group by strg_catgs.elemID  
) AS t  
INNER JOIN objects ON objects.ID IN (        
    select objects.ID         
    from objects         
    where objects.is_rel_strg>=1    
) GROUP BY objects.ID

查询结果示例 #

问题核心：仅筛选catID=14的数据，因此所有objects.ID的totalcost计算逻辑完全一致，结果自然相同。

错误的修改尝试 #

尝试将固定值14替换为objects.ID，但因关联逻辑混乱导致结果错误，错误SQL如下：

SELECT SUM(t.totalcost) AS totalcost ,objects.ID  
FROM (
    select sum(strg_op.opcost)/ sum(strg_op.opvalue * 1000) * strg_catgs.Disrate as totalcost    
    from objects
    inner join strg_catgs     
    inner join strg_op  on strg_op.ElemID = strg_catgs.elemID         
        and strg_catgs.elemID =(           
            SELECT strg_catgs.elemID           
            WHERE strg_catgs.catID =objects.ID        
        )  
        and strg_catgs.catID = objects.ID           
        and strg_catgs.descr = "cwe"           
        and objects.ID IN(Select objects.ID from objects where is_rel_strg>=1)    
    group by strg_catgs.elemID  
) AS t  
INNER JOIN objects ON objects.ID IN (        
    select objects.ID         
    from objects         
    where objects.is_rel_strg>=1) GROUP BY objects.ID

正确解决方案 #

问题出在子查询嵌套逻辑和分组关联的错误，正确做法是直接将objects与strg_catgs按ID=catID关联，分层计算成本：

方案一（支持嵌套聚合的数据库） #

SELECT 
    o.ID,
    SUM((SUM(sop.opcost) / SUM(sop.opvalue * 1000)) * sc.Disrate) AS totalcost
FROM objects o
INNER JOIN strg_catgs sc 
    ON o.ID = sc.catID 
    AND sc.descr = 'cwe'
INNER JOIN strg_op sop 
    ON sc.elemID = sop.elemID
WHERE o.is_rel_strg >= 1
GROUP BY o.ID

方案二（兼容所有数据库的子查询方式） #

如果你的数据库不支持嵌套聚合函数，可先计算每个elemID的单位成本，再关联汇总：

SELECT 
    o.ID,
    SUM(elem_cost.unit_cost * sc.Disrate) AS totalcost
FROM objects o
INNER JOIN strg_catgs sc 
    ON o.ID = sc.catID 
    AND sc.descr = 'cwe'
INNER JOIN (
    SELECT 
        elemID,
        SUM(opcost) / SUM(opvalue * 1000) AS unit_cost
    FROM strg_op
    GROUP BY elemID
) elem_cost ON sc.elemID = elem_cost.elemID
WHERE o.is_rel_strg >= 1
GROUP BY o.ID

逻辑说明 #

1. 直接从objects表出发，通过ID=catID关联strg_catgs，确保每个对象对应自己的分类数据
2. 关联strg_op获取操作成本和数值，按元素维度计算单位成本
3. 乘以Disrate后按objects.ID汇总，得到每个对象的专属总成本

内容的提问来源于stack exchange，提问作者youssef kroma