iT邦幫忙

0

MS-SQL的Join問題

ms sql
CooperWu 2021-03-27 00:56:31 ‧ 2285 瀏覽

  • 分享至 

  • xImage

https://ithelp.ithome.com.tw/upload/images/20210327/20122387VhXvPvzdcO.jpg

各位不好意思，今天想詢問一個SQL查詢的問題
上面的表每一天會有一筆資料，下面的表每小時會有一筆資料(可能要想像一下)
希望可以用MonitorID來Join兩張表。
上表第一筆資料的Stop_Date_Time為23號的15:00，第二筆是24號的15:00
那下表的23號16:00的那筆資料的SpanRef就是上表的第一筆資料
下表的第二筆資料是26號的17:00，那就是要取得上表的26號15:00的資料。
簡單來說就是利用下表的時間，去找出該資料是在上表的哪個時間點之後，
下表23號15:00之後的資料到24號14:59分的資料要對應到上表的第一筆，
下表24號15:00之後的要對應到上表的第二筆資料，以此類推。
最後的結果會像這樣
https://ithelp.ithome.com.tw/upload/images/20210327/20122387CzheAGxWDj.jpg

資料在這邊，感謝各位

declare @a table(
Stop_Date_Time dateTime
,MonitorID int
,SpanRef decimal(18,2)
)

declare @b table(
Date_Time dateTime
,MonitorID int
,Value decimal(18,2)
)

insert into @a
Values('2021-03-23 15:00','1','20')
,('2021-03-24 15:00','1','20')
,('2021-03-25 15:00','1','40')
,('2021-03-26 15:00','1','40')
,('2021-03-23 15:00','2','20')
,('2021-03-24 15:00','2','20')
,('2021-03-25 15:00','2','40')
,('2021-03-26 15:00','2','40')
,('2021-03-23 15:00','3','20')
,('2021-03-24 15:00','3','20')
,('2021-03-25 15:00','3','40')
,('2021-03-26 15:00','3','40')

insert into @b
Values('2021-03-23 16:00','1','30')
,('2021-03-26 17:00','1','50')
,('2021-03-23 16:00','2','30')
,('2021-03-26 17:00','2','50')
,('2021-03-23 16:00','3','30')
,('2021-03-26 17:00','3','50')
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
純真的人
iT邦大師 1 級 ‧ 2021-03-27 01:18:41
最佳解答

這樣吧..

select *
from @b b
left join @a a on a.MonitorID = b.MonitorID
and Convert(date,b.Date_Time) = Convert(date,a.Stop_Date_Time)

https://ithelp.ithome.com.tw/upload/images/20210327/20061369VGrlP0vLig.png

看更多先前的回應...收起先前的回應...
CooperWu iT邦新手 3 級 ‧ 2021-03-27 11:36:26 檢舉

謝謝您的回覆，不過這樣子做會有個問題，下表是每小時一筆資料，這樣的話，下表在26號15:00以前的值，必須要對應到上表的25號15:00的那筆才行。26號15:00之後的值到27號15:00的下表資料要對應上表26號15:00的值，以此類推

純真的人 iT邦大師 1 級 ‧ 2021-03-27 13:42:08 檢舉

邏輯先釐清...這樣對嗎?
3/25 對應範圍 3/24 15:00 ~ 3/26 14:00
3/26 對應範圍 3/26 15:00 ~ 3/27 14:00

所以條件
改這樣看看@@...

select *
from @b b
left join @a a on a.MonitorID = b.MonitorID
and b.Date_Time between a.Stop_Date_Time and dateadd(hour,-1,dateadd(d,1,a.Stop_Date_Time))

https://ithelp.ithome.com.tw/upload/images/20210327/20061369X5yig6L7Lj.png

CooperWu iT邦新手 3 級 ‧ 2021-03-27 13:47:30 檢舉

我在樓下有做一張圖，可以幫助理解，再麻煩您看看上方的部份，另外，上表的時間不會固定在15:00
https://ithelp.ithome.com.tw/upload/images/20210327/201223871IzwUqZ8qt.jpg

純真的人 iT邦大師 1 級 ‧ 2021-03-27 13:51:03 檢舉

時間不固定起來~SQL無法套用規則~抓範圍呢..
所以的條件應該是~當前日期時間~要再下筆的時間內吧~

例如：
2/26 08:00 2/26 10:00
2/26 08:00 2/27 06:00
2/26 08:00 2/27 11:00
2/27 12:00 2/27 13:00
2/27 12:00 2/27 16:00

對吧??

純真的人 iT邦大師 1 級 ‧ 2021-03-27 14:31:04 檢舉

你在看看~
因為9999這行~有時間不確定~我把@a的
('2021-03-23 15:00','1','20')
刪除了~才顯示你要的結果~

declare @a table(
Stop_Date_Time dateTime
,MonitorID int
,SpanRef decimal(18,2)
)

declare @b table(
Date_Time dateTime
,MonitorID int
,Value decimal(18,2)
)

insert into @a
Values('2021-03-24 15:00','1','20')
,('2021-03-25 15:00','1','40')
,('2021-03-26 15:00','1','40')
,('2021-03-23 15:00','2','20')
,('2021-03-24 15:00','2','20')
,('2021-03-25 15:00','2','40')
,('2021-03-26 15:00','2','40')
,('2021-03-23 15:00','3','20')
,('2021-03-24 15:00','3','20')
,('2021-03-25 15:00','3','40')
,('2021-03-26 15:00','3','40')
,('2021-03-22 13:00','1','7777')
,('2021-03-23 08:00','1','9999')
,('2021-03-24 17:00','1','1111')

insert into @b
Values('2021-03-23 16:00','1','30')
,('2021-03-26 17:00','1','50')
,('2021-03-23 16:00','2','30')
,('2021-03-26 17:00','2','50')
,('2021-03-23 16:00','3','30')
,('2021-03-26 17:00','3','50')
,('2021-03-26 11:00','1','50')
,('2021-03-22 16:00','1','4444') --7777
,('2021-03-23 10:00','1','3333') --9999
,('2021-03-24 15:00','1','5555') --9999
,('2021-03-24 18:00','1','9999') --1111

select Date_Time
,a.MonitorID
,Value
,Stop_Date_Time
,Lest_Date_Time
,k.MonitorID
,SpanRef
from @b a
left join (
	select *
	,isNull((
		select top 1 dateadd(hour,-1,b.Stop_Date_Time)
		from @a b
		where a.Stop_Date_Time < b.Stop_Date_Time
		and a.MonitorID = b.MonitorID
		order by b.Stop_Date_Time
	),dateadd(d,1,Stop_Date_Time)) Lest_Date_Time
	from @a a
) k on dateadd(hour,-1,Date_Time) between Stop_Date_Time and Lest_Date_Time
and k.MonitorID = a.MonitorID

https://ithelp.ithome.com.tw/upload/images/20210327/200613694IskrvHVm0.png

CooperWu iT邦新手 3 級 ‧ 2021-03-27 15:28:39 檢舉

你真的太厲害了，這個就是我想要的結果，感謝你

登入發表回應
1
rogeryao
iT邦超人 7 級 ‧ 2021-03-27 12:08:04 
create table aa (
Stop_Date_Time dateTime
,MonitorID int
,SpanRef decimal(18,2)
);

insert into aa
Values('2021-03-23 15:00','1','20')
,('2021-03-24 15:00','1','20')
,('2021-03-25 15:00','1','40')
,('2021-03-26 15:00','1','40')
,('2021-03-23 15:00','2','20')
,('2021-03-24 15:00','2','20')
,('2021-03-25 15:00','2','40')
,('2021-03-26 15:00','2','40')
,('2021-03-23 15:00','3','20')
,('2021-03-24 15:00','3','20')
,('2021-03-25 15:00','3','40')
,('2021-03-26 15:00','3','40')
-- 額外加入
,('2025-03-23 08:00','1','7777');

create table bb (
Date_Time dateTime
,MonitorID int
,Value decimal(18,2)
);

insert into bb
Values('2021-03-23 16:00','1','30')
,('2021-03-26 17:00','1','50')
,('2021-03-23 16:00','2','30')
,('2021-03-26 17:00','2','50')
,('2021-03-23 16:00','3','30')
,('2021-03-26 17:00','3','50')
-- 額外加入
,('2025-03-24 14:59','1','3333');

SELECT xx.Date_Time,xx.MonitorID,xx.Value,yy.Stop_Date_Time,yy.SpanRef
FROM (
SELECT CONVERT(varchar(12), DATEADD(hour,-15,bb.Date_Time), 112) AS Date_Time_New,
bb.Date_Time,bb.MonitorID,bb.Value
FROM bb) AS xx
LEFT JOIN (
SELECT CONVERT(varchar(12), aa.Stop_Date_Time, 112) AS Stop_Date_Time_New,
aa.Stop_Date_Time,aa.MonitorID,aa.SpanRef
FROM aa
) AS yy ON yy.Stop_Date_Time_New=xx.Date_Time_New AND yy.MonitorID=xx.MonitorID

Demo

看更多先前的回應...收起先前的回應...
CooperWu iT邦新手 3 級 ‧ 2021-03-27 12:25:46 檢舉

感謝您願意花時間回覆，答案很接近了，不過這邊可能要跟您道個歉，我漏講了一個重點，就是上表雖然是一天一筆資料，可是時間不會固定在15:00，有可能是當天的任何一個時間，請問這樣還有辦法處理嗎？

rogeryao iT邦超人 7 級 ‧ 2021-03-27 12:38:16 檢舉

"上表雖然是一天一筆資料，可是時間不會固定在15:00"
==>
上表 aa
-- 額外加入
,('2025-03-23 08:00','1','7777');
不是這樣嗎?

CooperWu iT邦新手 3 級 ‧ 2021-03-27 13:04:46 檢舉

我有試一下，大概是這樣
https://ithelp.ithome.com.tw/upload/images/20210327/201223871IzwUqZ8qt.jpg

rogeryao iT邦超人 7 級 ‧ 2021-03-28 05:24:25 檢舉

若題意為 :
(1) aa.MonitorID=bb.MonitorID
(2) aa.Stop_Date_Time<=bb.Date_Time
(3) 由 2 產生的資料再找出 aa.Stop_Date_Time 最接近 bb.Date_Time 的資料

SELECT *
FROM (SELECT bb.*,aa.Stop_Date_Time,aa.SpanRef,
ROW_NUMBER() OVER (PARTITION BY bb.Date_Time,bb.MonitorID ORDER BY Stop_Date_Time DESC) AS ROWNUM
FROM bb
LEFT JOIN aa ON aa.MonitorID=bb.MonitorID AND aa.Stop_Date_Time<=bb.Date_Time) AS mm
WHERE mm.ROWNUM=1

Demo

CooperWu iT邦新手 3 級 ‧ 2021-03-29 15:04:48 檢舉

感謝你的回覆，這個也是可行的方式，謝謝

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22198 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙