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 uniqueidentifier default newid(),StopName nvarchar(20),ArrivalTiem datetime);
Insert Into StopRecords (StopName,ArrivalTiem)
Values
	('A','2021-01-14 08:00'),
	('B','2021-01-14 08:10'),
	('C','2021-01-14 08:20'),
	('D','2021-01-14 08:30'),
--	('E','2021-01-14 08:40'),
	('A','2021-01-14 08:50')
Select * From StopRecords


R_Id
StopName
ArrivalTiem


f1d58346-ba65-4a01-b802-6797c8627b3b
A
2021-01-14 08:00:00.000

5e1ae7bc-4b6a-4a6f-86f4-cddf2b184514
B
2021-01-14 08:10:00.000

2f3032c1-0878-452a-90da-2e8d0ad5561b
C
2021-01-14 08:20:00.000

ba1e1f49-a764-4de0-8596-592db5c60e55
D
2021-01-14 08:30:00.000

db2bbc1d-993e-478a-a1ac-7e9552113e0b
A
2021-01-14 08:50:00.000


-- 方法三
select A.StopName,C.ArrivalTiem
from (
select StopName,
ROW_NUMBER() over(partition by StopName order by StopSeq) as ArrivalOrder,
StopSeq
from Stops) as A
left join (
select B.StopName,
ROW_NUMBER() over(partition by B.StopName order by B.ArrivalTiem) as ArrivalOrder,
B.ArrivalTiem
from StopRecords as B
) as C on C.StopName=A.StopName and C.ArrivalOrder=A.ArrivalOrder
order by A.StopSeq,C.ArrivalTiem


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
null

A
2021-01-14 08:50:00.000

R_Id	StopName	ArrivalTiem
f1d58346-ba65-4a01-b802-6797c8627b3b	A	2021-01-14 08:00:00.000
5e1ae7bc-4b6a-4a6f-86f4-cddf2b184514	B	2021-01-14 08:10:00.000
2f3032c1-0878-452a-90da-2e8d0ad5561b	C	2021-01-14 08:20:00.000
ba1e1f49-a764-4de0-8596-592db5c60e55	D	2021-01-14 08:30:00.000
db2bbc1d-993e-478a-a1ac-7e9552113e0b	A	2021-01-14 08:50:00.000