SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table Stops(StopName nvarchar(20), --站點名稱
StopSeq int --順序
);
Insert Into Stops (StopName,StopSeq)
Values 
    ('A',1),
    ('B',2),
    ('C',3),
    ('D',4),
    ('E',5),
    ('A',6);

Select * From Stops;


StopName
StopSeq


A
1

B
2

C
3

D
4

E
5

A
6


Create Table StopRecords(R_Id int, --流水號
StopName nvarchar(20), --站點名稱
ArrivalTiem datetime --到站時間
);
Insert Into StopRecords (R_Id,StopName,ArrivalTiem)
Values
	(1,'A','2021-01-14 08:00'),
	(2,'B','2021-01-14 08:10'),
	(3,'C','2021-01-14 08:20'),
	(4,'D','2021-01-14 08:30'),
	(5,'E','2021-01-14 08:40'),
	(6,'A','2021-01-14 08:50');
	
Select * From StopRecords;


R_Id
StopName
ArrivalTiem


1
A
2021-01-14 08:00:00.000

2
B
2021-01-14 08:10:00.000

3
C
2021-01-14 08:20:00.000

4
D
2021-01-14 08:30:00.000

5
E
2021-01-14 08:40:00.000

6
A
2021-01-14 08:50:00.000


-- 方法一
select A.StopName,
max(B.ArrivalTiem) over(partition by B.StopName,A.StopSeq) as ArrivalTiem
from Stops as A
left join StopRecords as B on B.StopName=A.StopName
and B.R_Id=A.StopSeq
order by ArrivalTiem,A.StopName


StopName
ArrivalTiem


A
2021-01-14 08:00:00.000

B
2021-01-14 08:10:00.000

C
2021-01-14 08:20:00.000

D
2021-01-14 08:30:00.000

E
2021-01-14 08:40:00.000

A
2021-01-14 08:50:00.000


-- 方法二
select A.StopName,
max(B.ArrivalTiem) as ArrivalTiem
from Stops as A
left join StopRecords as B on B.StopName=A.StopName
and B.R_Id=A.StopSeq
group by B.ArrivalTiem,A.StopName
order by B.ArrivalTiem,A.StopName


StopName
ArrivalTiem


A
2021-01-14 08:00:00.000

B
2021-01-14 08:10:00.000

C
2021-01-14 08:20:00.000

D
2021-01-14 08:30:00.000

E
2021-01-14 08:40:00.000

A
2021-01-14 08:50:00.000

R_Id	StopName	ArrivalTiem
1	A	2021-01-14 08:00:00.000
2	B	2021-01-14 08:10:00.000
3	C	2021-01-14 08:20:00.000
4	D	2021-01-14 08:30:00.000
5	E	2021-01-14 08:40:00.000
6	A	2021-01-14 08:50:00.000