php - 根据使用情况查询不同费率类别的总金额

Question

场景:我需要根据他们提供/接受不同费率类别的服务的小时费率和时间(分钟)来计算适用于每个客户的金额总和。

使用以下查询我得到以下结果:

SELECT pc.client_id, pr.hourly_rate, (SUM(tt.time_end) - SUM(tt.time_start)) as Difference
FROM  track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id 
GROUP BY hourly_rate

结果

client_id   hourly_rate   Difference
1           50            6360
1           23            4080
1           43            5760
6           13            34680
7           60            32400
7           14            23340

我想弄清楚如何首先我需要使用一个函数将 DIFFERENCE 结果转换为可读小时数，然后将转换后的结果乘以它各自的 hourly_rate，然后得到每个客户的 SUM。

我首先尝试了这个(如下)，它非常接近，但不准确。我认为它不准确，因为我需要先转换 DIFFERENCE 列。

SELECT SUM(Difference2) AS total
FROM (SELECT pc.client_id, pr.hourly_rate, (SUM(tt.time_end) - SUM(tt.time_start)) as Difference, ((SUM(tt.time_end) - SUM(tt.time_start)) * pr.hourly_rate) as Difference2
FROM  track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id   
GROUP BY hourly_rate) AS table1

有什么想法吗？

Answer 1

这有效:

SELECT SUM(T) as YES FROM (
SELECT pc.client_id, pr.hourly_rate, ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) as Difference, (ROUND((( SUM(tt.time_end) - SUM(tt.time_start))/3600),2) * hourly_rate) as T
FROM track_time as tt, project_track as pt, project as pr, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pr.project_id
AND pr.project_id = pc.project_id
AND pc.client_id = cl.client_id
AND cl.client_id = " . $db->prep($client_id) . " GROUP BY hourly_rate) as table1