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

ms sql

JT 2021-09-09 15:56:30 ‧ 1179 瀏覽

分享至

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

--假設目前時間@nowTime=05:10，則篩選出

--假設目前時間@nowTime=07:10，則篩選出
RouteName LineName LineTime

--假設目前時間@nowTime=16:10，則篩選出
RouteName LineName LineTime

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

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

登入發表討論

直播研討會

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

直播中

3 個回答

qaz11226633

iT邦新手 4 級 ‧ 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

回應 1
分享
檢舉

JT iT邦新手 1 級 ‧ 2021-09-09 16:56:21 檢舉

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

登入發表回應

rogeryao

iT邦超人 8 級 ‧ 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

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

JT iT邦新手 1 級 ‧ 2021-09-10 09:20:00 檢舉

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

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

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

rogeryao iT邦超人 8 級 ‧ 2021-09-10 09:24:55 檢舉

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

qaz11226633 iT邦新手 4 級 ‧ 2021-09-10 10:00:33 檢舉

好像也不能刪= =

登入發表回應

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