iT邦幫忙

0

MS SQL 篩選最近的時間但保留最後一筆的問題

JT 2021-09-09 15:56:30622 瀏覽

各位前輩大家好,我有一個Table紀錄RouteName、LineName和LineTime,一條RouteName會對應多條LineName,我想篩選離目前時間最近的RouteName,LineName,LineTime
條件1: 篩選離目前時間最近的LineName,但是目前時間要>=LineTime
條件2: 若時間大於最後一個LineName,則保留最後一個LineName
條件3: 若時間不在時間內,則一條RouteName保留最後一個LineName

--假設目前時間@nowTime=05:10,則篩選出
https://ithelp.ithome.com.tw/upload/images/20210909/20129970Qjz5ULFbC0.jpg

--假設目前時間@nowTime=07:10,則篩選出
RouteName LineName LineTime
https://ithelp.ithome.com.tw/upload/images/20210909/20129970erWQqdDm2M.jpg

--假設目前時間@nowTime=16:10,則篩選出
RouteName LineName LineTime
https://ithelp.ithome.com.tw/upload/images/20210909/20129970Qjz5ULFbC0.jpg

Declare @nowTime datetime = '2021-09-09 16:10'

Drop Table If Exists Lines
Create Table Lines( RouteName nvarchar(20),LineName nvarchar(20),LineTime datetime);
Insert Into Lines (RouteName,LineName,LineTime)
Values 
    ('A','A01','2021-09-09 07:00'),
    ('A','A02','2021-09-09 08:00'),
    ('A','A03','2021-09-09 09:00'),
    ('A','A04','2021-09-09 10:00'),
    ('A','A05','2021-09-09 11:00'),
	('B','B01','2021-09-09 12:00'),
	('B','B02','2021-09-09 13:00'),
	('B','B03','2021-09-09 14:00'),
	('B','B04','2021-09-09 15:00'),
	('B','B05','2021-09-09 16:00')

請各位邦友幫幫忙,感謝~

1
qaz11226633
iT邦新手 5 級 ‧ 2021-09-09 16:08:47
最佳解答

這樣?

DECLARE @Lines Table(LineName nvarchar(20),LineTime datetime);
Insert Into @Lines (LineName,LineTime)
Values 
    ('A01','2021-09-09 07:00'),
    ('A02','2021-09-09 08:00'),
    ('A03','2021-09-09 09:00'),
    ('A04','2021-09-09 10:00'),
    ('A05','2021-09-09 11:00'),
	('A06','2021-09-09 12:00'),
	('A07','2021-09-09 13:00'),
	('A08','2021-09-09 14:00'),
	('A09','2021-09-09 15:00'),
	('A10','2021-09-09 16:00')

	SELECT TOP(1) LineName FROM @Lines WHERE LineTime <= (SELECT Convert(varchar(10),Getdate(),111) + ' 07:50') ORDER BY LineTime DESC
	SELECT TOP(1) LineName FROM @Lines WHERE LineTime <= (SELECT Convert(varchar(10),Getdate(),111) + ' 16:10') ORDER BY LineTime DESC
JT iT邦新手 5 級 ‧ 2021-09-09 16:56:21 檢舉

感謝qaz11226633的回覆
這個有解決部分問題,剛剛有修改一下題目,因為還有不同Route與不在時間範圍內的都要保留最後一筆

3
rogeryao
iT邦大師 1 級 ‧ 2021-09-09 20:00:57
Create Table Lines( RouteName nvarchar(20),LineName nvarchar(20),LineTime datetime);

Insert Into Lines (RouteName,LineName,LineTime)
Values 
('A','A01','2021-09-09 07:00'),
('A','A02','2021-09-09 08:00'),
('A','A03','2021-09-09 09:00'),
('A','A04','2021-09-09 10:00'),
('A','A05','2021-09-09 11:00'),
('B','B01','2021-09-09 12:00'),
('B','B02','2021-09-09 13:00'),
('B','B03','2021-09-09 14:00'),
('B','B04','2021-09-09 15:00'),
('B','B05','2021-09-09 16:00');
WITH CTE_X1 AS (
SELECT RouteName,'2021-09-09 05:10' AS RouteTime
FROM Lines
UNION
SELECT RouteName,'2021-09-09 07:10' AS RouteTime
FROM Lines
UNION
SELECT RouteName,'2021-09-09 16:10' AS RouteTime
FROM Lines
)

SELECT Z.*,K.RouteTime
FROM (
SELECT X.RouteName,X.RouteTime,
CASE WHEN X.RouteTime<MIN(Y.LineTime) OR X.RouteTime>=MAX(Y.LineTime)
THEN MAX(Y.LineTime)
ELSE 
(SELECT LineTime
FROM (
SELECT *,LEAD(LineTime, 1, DATEADD(D, 10, LineTime)) OVER (PARTITION BY RouteName ORDER BY LineTime) AS LineTimeEnd
FROM Lines) AS P
WHERE X.RouteTime>P.LineTime AND X.RouteTime<=P.LineTimeEnd
AND X.RouteName=P.RouteName)
END AS LineTimeTemp
FROM CTE_X1 AS X
LEFT JOIN Lines AS Y ON Y.RouteName=X.RouteName
GROUP BY X.RouteName,X.RouteTime) AS K
LEFT JOIN Lines AS Z ON Z.RouteName=K.RouteName AND Z.LineTime=K.LineTimeTemp
ORDER BY K.RouteTime,Z.RouteName

Demo

看更多先前的回應...收起先前的回應...
JT iT邦新手 5 級 ‧ 2021-09-10 09:20:00 檢舉

感謝rogeryao的回覆
這個就是我要的答案,每每都能從rogeryao學到不同的技巧與思考方式,真是非常感謝你~

JT iT邦新手 5 級 ‧ 2021-09-10 09:21:42 檢舉

這篇才是我的最佳解答,剛剛選錯了,有辦法修改嗎?

rogeryao iT邦大師 1 級 ‧ 2021-09-10 09:24:55 檢舉

無法修改,沒關係,問題有解決就好了。

好像也不能刪= =

0
allenlwh
iT邦高手 1 級 ‧ 2021-09-09 21:58:18

條件3: 若時間不在時間內,則一條RouteName保留最後一個LineName

declare @tme varchar(10)
declare @nowtme datetime

set @tme='05:10'
set @nowtme=convert(datetime,convert(varchar,getdate(),112)+' '+@tme+':00')

select case when 
(select count(*) from Lines
	where @nowtme>LineTime and @nowtme<LineTime)=0
then 
	(select top 1 LineName from Lines order by LineTime desc)
end as LineName
JT iT邦新手 5 級 ‧ 2021-09-10 09:25:45 檢舉

感謝allenlwh的回覆

我要發表回答

立即登入回答