MS SQL 重複站點時間記錄問題

ms sql

JT 2021-01-14 16:06:11 ‧ 894 瀏覽

各位邦友好，
我有兩個table，一個紀錄站點(站點名稱、順序)，
一個紀錄到達站點的時間(流水號、站點名稱、到站時間)
站點table

Drop Table If Exists Stops
Create Table Stops(StopName nvarchar(20),StopSeq int);
Insert Into Stops (StopName,StopSeq)
Values 
    ('A',1),
    ('B',2),
    ('C',3),
    ('D',4),
    ('E',5)

Select * From Stops

站點資料

紀錄到達站點時間table

Drop Table If Exists StopRecords
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

到達站點資料

原本站點沒重複的話，我只要用Left Join和MAX就可以取得站點的最後抵達時間

Select S.StopName,MAX(R.ArrivalTiem) AS 'MaxArrivalTime' From Stops S
Left Join StopRecords R On S.StopName = R.StopName
Group By S.StopName

站點不重複結果

但是如果站點重複的話(ex: 又要到A點)，請問要怎麼取得站點的最後抵達時間呢?

Drop Table If Exists Stops
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)

預期得到的答案

如果沒有配對的站點時間，會顯示NULL
ex:

Drop Table If Exists StopRecords
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'),
	('A','2021-01-14 08:50')
Select * From StopRecords

再請各位SQL高手幫幫忙，謝謝

看更多先前的討論...收起先前的討論...

柯柯 iT邦新手 3 級 ‧ 2021-01-14 16:15:29 檢舉

把 S.StopSeq 也加入GROUP BY 裡面
Select S.StopName,MAX(R.ArrivalTiem) AS 'MaxArrivalTime' From Stops S
Left Join StopRecords R On S.StopName = R.StopName
Group By S.StopName,S.StopSeq

JT iT邦新手 1 級 ‧ 2021-01-14 16:41:01 檢舉

感謝柯柯的回覆~
不過第1筆站點A與第6筆站點A都取得了08:50的時間
應該是第1筆站點A取得紀錄裡的第1筆站點A的紀錄08:00
第2筆站點A取得紀錄裡的第2筆站點A的紀錄08:50

柯柯 iT邦新手 3 級 ‧ 2021-01-14 17:11:20 檢舉

抱歉我沒注意到第一個時間錯誤

另外我回復好了比較清楚

JT iT邦新手 1 級 ‧ 2021-01-14 17:36:22 檢舉

就是因為有可能A走完又回到A，又只是順序不同才令人傷腦筋。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-01-14 16:25:56

最佳解答

假若 Stops 的 StopSeq(順序)=StopRecords 的 R_Id(流水號)

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);

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 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

-- 方法二
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

Demo

回應 3
分享
檢舉

JT iT邦新手 1 級 ‧ 2021-01-14 17:03:52 檢舉

感謝rogeryao的回覆
這個是最接近我要的答案，不過實際上Table StopRecords的R_Id是用newid()，所以應該要改一下題目

Drop Table If Exists StopRecords
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

rogeryao iT邦超人 8 級 ‧ 2021-01-14 17:37:03 檢舉

Stops 的 StopSeq(順序) 與 StopRecords 的 R_Id(流水號)無關

-- 方法三
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

Demo

JT iT邦新手 1 級 ‧ 2021-01-14 17:56:34 檢舉

感謝rogeryao的回覆
這就是我想要的答案，非常感謝
用ROW_NUMBER多一個ArrivalOrder, 又學了一招

登入發表回應

㊣浩瀚星空㊣

iT邦大神 1 級 ‧ 2021-01-14 16:19:44

第一招，記錄來對應格式

Select S.StopName,MAX(R.ArrivalTiem) AS 'MaxArrivalTime' From StopRecords R
Left Join Stops S On S.StopName = R.StopName
Group By R.StopName

缺點是沒記錄的不會出來。

第二招是子查尋模式。不過MSSQL的寫法我不太會
我提供MYSQL的寫法給你試試

Select S.StopName,R.MaxArrivalTime From Stops S
Left Join (SELECT StopName,MAX(R.ArrivalTiem) AS MaxArrivalTime StopRecords Group By S.StopName) AS  R On S.StopName = R.StopName

回應 1
分享
檢舉

JT iT邦新手 1 級 ‧ 2021-01-14 17:17:21 檢舉

感謝星空大的回覆
不過我每個站點都要列出來，如果沒有對應時間的站點會顯示NULL

登入發表回應

柯柯

iT邦新手 3 級 ‧ 2021-01-14 17:27:54

第二張表我要怎確定 8:00 是seq =1的站而不是seq =6的站
因為ID看起來是流水號
所以第二張表沒有欄位去記錄他是順序幾
如果說第二張表沒有紀錄順序
我猜應該是沒辦法JOIN出來吧(不確定可能要看大神解答)

另外寫一下第二張表也有紀錄順序的

Select S.StopName,MAX(R.ArrivalTiem) AS 'MaxArrivalTime' From Stops S
Left Join StopRecords R On S.StopName = R.StopName AND S.StopSeq = R.StopSeq
Group By S.StopName,S.StopSeq

有時間NULL的一樣是第二張表有紀錄順序的寫法

Select 
  S.StopName,
  CAST(CASE WHEN R.ArrivalTiem IS NULL then 'NULL' else MAX(CONVERT(MAX(CONVERT(VARCHAR(20),R.ArrivalTiem,120))) END as varchar(50)) AS 'MaxArrivalTime' 
From Stops S
Left Join StopRecords R On S.StopName = R.StopName AND S.StopSeq = R.StopSeq
Group By S.StopName,S.StopSeq, R.ArrivalTiem 
ORDER BY S.StopSeq

DEMO