mysql - 如何创建表+计算成本涉及2列

Question

我想按阶段计算成本，但它不起作用我的数据如下。我正在使用这个命令来计算成本

SELECT Daily.dayno, activity.class, activity.phase,
    activity.duration/sum(activity.duration)*daily.daycost as cost
from Daily INNER JOIN
(
    Select activity.dailyuid, sum(activity.duration) duration
    group by dailyUID
) Activity
on activity.dailyuid=daily.dailyuid
group by class, phase

显然上面的查询是行不通的。例如，我想要的是按阶段计算成本

Dailyuid DD1 中的阶段 SS1 = 4/12*1000，其中 4 是 SS1 的总小时数，12 是 dailyuid DD1 的总小时数，1000 是 dailyuid DD1 的成本。

另一个例子是阶段 ST2 = 8/12*1000IH=10.6/11.6*2000等

每日表

DailyUID | Dayno | Daycost |
---------|-------|---------|
DD1      | 1     | 1000    | 
DD2      | 2     | 2000    |
DD3      | 3     | 3000    |

事件表

DailyUID | CLASS | PHASE | Duration |
-----------------------------------
DD1      | TS    | SS1   | 3        |
DD1      | TS    | SS1   | 1        |
DD1      | TS    | ST2   | 2        |
DD1      | P     | ST2   | 6        |
DD2      | P     | IH1   | 6.6      |
DD2      | U     | IH1   | 4        |
DD2      | TS    | IH2   | 1        |
DD3      | TU    | SC1   | 7        |
DD3      | P     | SC2   | 8        |
DD3      | U     | CMPLT | 3        |

Answer 1

如果我没理解错的话，您需要在加入之前预先聚合表。需要一个额外的聚合:

select ap.phase,
       (ap.duration / ad.duration) * d.daycost as cost
from (SELECT d.dailyUid, sum(daycost) as daycost
      FROM Daily d
      GROUP BY d.dailyuid
     ) d INNER JOIN
     (Select a.dailyuid, sum(a.duration) as duration
      from activity a
      group by dailyUID
     ) ad
     on ad.dailyuid = d.dailyuid join
     (select a.dailyuid, a.phase, sum(a.duration) as duration
      from activity a
      group by a.dailyuid, a.phase
     ) ap
     on ad.dailyuid = ap.dailyuid
group by phase;

SQL Fiddle 是 here .