mysql - 基于多个表的排名

Question

select a.no, a.Dtime,count(b.Dtime)+1 as Rank 
from table1 a left 
join table1 b on a.Dtime>b.Dtime and a.no=b.no 
group by a.no,a.Dtime 
order by a.no, a.Dtime

表 1 输入:

NO  Dtime
1   08:10:00
1   09:10:00
1   09:40:00
1   10:10:00
2   09:30:00
2   10:15:00
3   09:00:00

输出:

NO  Dtime       Rank
1   08:10:00    1
1   09:10:00    2
1   09:40:00    3
1   10:10:00    4
2   09:30:00    1
2   10:15:00    2
3   09:00:00    1

但我在 mysql 中寻找输出，其中 table2 Rank 链接到 table1 和 table2 Dtime，即 table1.Dtime>table2.time

table2输入

NO  Dtime   
1   08:30:00    
1   09:15:00    
1   09:50:00    
2   08:30:00    
2   09:45:00    
3   09:50:00

输出:

NO  table1.Dtime    Rank    table2.Dtime
1   08:10:00        0       00:00:00 
1   09:10:00        1       08:30:00
1   09:40:00        2       09:15:00
1   10:10:00        3       09:50:00
2   09:30:00        1       08:30:00
2   10:15:00        2       09:45:00
3   09:00:00        0       00:00:00

Answer 1

您可以对初始查询使用相同的方法。只是 left join 到 table2。要从 table2 获取 Dtime，您可以使用相关子查询:

select a.no, a.Dtime,
       count(b.Dtime) as Rank,
       coalesce((select c.Dtime
       from table2 as c       
       where c.no = a.no and a.Dtime > c.Dtime
       order by c.Dtime desc limit 1), '00:00:00') as t2Dtime
from table1 a 
left join table2 b on a.Dtime > b.Dtime and a.no = b.no 
group by a.no,a.Dtime 
order by a.no, a.Dtime

Demo here