mysql - 连接引用表后 SUM 结果出现问题

Question

早上好，

我遇到查询问题。我正在尝试对 UTC 时间范围内的列进行求和。当我仅查询具有确切 UTC 值的源表时，它按预期工作。一旦我添加联接，调度表添加一些进一步的详细信息，结果就会不正确。以下是我正在使用的查询和表结构。任何帮助将不胜感激。

查询1:

select logid,SUM(ti_availtime) as "Total Avail TIme" 
from status
where logid = 12345 
and  status.starttime_utc 
between 1528070400 and 1528102800
group by logid

logid   Total Avail Time
12345   20106

状态表的表结构:

Date    Logid   Starttime_utc   Ti_availtime    Interval
2018-06-04  12345   1528070400  106 30
2018-06-04  12345   1528070401  5000    30
2018-06-04  12345   1528070402  5000    30
2018-06-04  12345   1528102800  10000   30

注意:UTC 值(1528070401、1528070402)只是示例*

时间表的表结构:

Date    Logid   Name    Starttime_utc   Stoptime_utc
2018-06-04  12345   John Smith  1528070400  1528102800

查询2:

select schedule.Analyst, SUM(status.ti_availtime) 
from status
join schedule on status.logid = schedule.logid
where schedule.logid = 30120 and  status.starttime_utc between schedule.starttime_utc and schedule.starttime_utc 
group by vqschedule.row_date

结果:

Analyst Total Avail Time
John Smith  1351027

Answer 1

完全同意@jspcal，并且在第一个查询中还有一件事，您的项目 status.logid 但在第二个项目计划中。分析师，我只是编写完整的查询

select status.logid, SUM(status.ti_availtime) 
from status
join schedule on status.logid = schedule.logid
where schedule.logid = 30120 and  status.starttime_utc between schedule.starttime_utc and schedule.stoptime_utc
group by status.logid