如何避免在此SQL查询中出现过多计数？

Question

Trains表是：

trains:
tid | dept | dest | time
 3    SF      LA     8
 33   SF      LA     10

Passenger表是：

pass:
pid | dept | dest | time
100   SF     LA     7
101   SF     LA     8
102   SF     LA     9

我想知道登上火车的乘客人数。我当前的查询是：

select tid,trains.dept,trains.dest, count(distinct pid)
from
trains inner join pass
on
trains.dept=pass.dept
and
trains.dest=pass.dest
and
trains.time>=pass.time
group by
tid,trains.dept,trains.dest

查询是错误的，因为它正在重新计算已经乘坐较早的火车前往目的地的乘客。如何避免这种过度计算？

预期结果：

tid number_of_passengers
3   2
33  1

我得到的结果是：

tid number_of_passengers
3    2
33   3

例如，tid 3只正确显示了2名登机乘客，而tid 33(同一目的地)错误地显示了2+1(2来自tid 3)。

Answer 1

假设有人登上了下一班火车。您可以使用相关子查询获取“下一列”列车：

select p.*,
       (select t.tid
        from trains t
        where t.dept = p.dept and t.dest = p.dest and t.time >= p.time
        order by t.time asc
        limit 1
       ) as tid
from pass p;

一旦您有了这些信息，每个列车的摘要就是一个聚合：

select tid, count(*)
from (select p.*,
             (select t.tid
              from trains t
              where t.dept = p.dept and t.dest = p.dest and t.time >= p.time
              order by t.time
              limit 1
             ) as tid
      from pass p
     ) p
group by tid;