MS ACCESS如何平均2个表中的3列

发布于 2024-11-04 23:20:47 字数 499 浏览 0 评论 0原文

我有以下表格：

T1:
Repair_ID, Descreption,Cost,Level
(1,a,24,9)
(2,b,34,9)
(3,a,22,3)
(4,c,11,6)

T2:
Repair_ID, Start_Time, End_Time,Location_ID
(1,02:00:00,03:00:00,3)
(2,04:00:00,05:00:00,7)
(3,06:00:00,08:00:00,3)

我希望能够获得一个表格，其中包含平均成本、平均水平和平均维修持续时间（通过从开始时间减去结束时间）按描述和位置 ID 分组所以表格看起来像这样：描述、Location_ID、平均成本、平均级别、平均持续时间 (a,3,(24+22/2)=24,(9+3/2=6), 01:30:00*)

Repair_ID 1 为 1 小时，repair_ID 3 为 2 小时 = 01:30:00平均值，因为这两次修复发生在同一位置并且具有相同的描述谢谢

原文

I have the following tables:

T1:
Repair_ID, Descreption,Cost,Level
(1,a,24,9)
(2,b,34,9)
(3,a,22,3)
(4,c,11,6)

T2:
Repair_ID, Start_Time, End_Time,Location_ID
(1,02:00:00,03:00:00,3)
(2,04:00:00,05:00:00,7)
(3,06:00:00,08:00:00,3)

I want to be able to get a table that contains an average of the cost, an average of the level, and an average of the duration of the repair(by subtracting end_Time from start_time) grouping by Descreption and Location_ID
so the table would look like this:
Descreption, Location_ID, AvgCost,AvgLevel, Avg Duration
(a,3,(24+22/2)=24,(9+3/2=6), 01:30:00*)

1 hour for repair_ID 1 and 2 hours for repair_ID 3 = 01:30:00 on average since these two repairs happened in the same location and have the same descreption
thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

以歌曲疗慰 2024-11-11 23:20:47

这实际上非常简单。连接三个表并执行您所描述的计算和分组

更新设置日期差异平均值的格式有点棘手。您需要在执行聚合后进行格式化。最简单的方法是使用内联视图

SELECT 
     Descreption,
     Location_id,
     AvgLevel,
     AvgCost,
     Format(CDATE(AvgDuration),"hh:mm:ss") AvgDuration
FROM
(
    SELECT
        repair.Descreption,
        location.Location_id,
        AVG(repair.level) AvgLevel,
        AVG(repair.Cost) AvgCost,
        AVG(times.end_time - times.start_time)  AvgDuration 

    FROM 
        T3 location
        INNER JOIN t1 repair
        on location.repair_id = repair.repair_id
        INNER JOIN t2 times
        ON location.repair_id = times.repair_id
    GROUP BY
        repair.Descreption,
        location.Location_id) foo
ORDER BY
       AvgCost desc

This actually pretty straight forward. Join the three tables and do the calculations and grouping you've described

Update Formatting an average of Difference of dates is a little tricky. You need to do the format after the Aggregate has been performed. The easiest way to do this is to use an in line view

SELECT 
     Descreption,
     Location_id,
     AvgLevel,
     AvgCost,
     Format(CDATE(AvgDuration),"hh:mm:ss") AvgDuration
FROM
(
    SELECT
        repair.Descreption,
        location.Location_id,
        AVG(repair.level) AvgLevel,
        AVG(repair.Cost) AvgCost,
        AVG(times.end_time - times.start_time)  AvgDuration 

    FROM 
        T3 location
        INNER JOIN t1 repair
        on location.repair_id = repair.repair_id
        INNER JOIN t2 times
        ON location.repair_id = times.repair_id
    GROUP BY
        repair.Descreption,
        location.Location_id) foo
ORDER BY
       AvgCost desc

回复收藏 0 原文

~没有更多了~