Re: [SQL ] join on 條件為「最接近的值」？

看板Database (資料庫)作者cutekid (可愛小孩子)時間8年前 (2016/06/22 14:50)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串3/3 (看更多)

create table #TABLE_A( id nvarchar(3), name nvarchar(2), eve_d nvarchar(4), event nvarchar(3) ) create table #TABLE_B( id nvarchar(3), date nvarchar(4), score numeric(28,2) ) -- 建立測資 insert into #TABLE_A select '001','小華','0616','事件A' insert into #TABLE_A select '002','小明','0618','事件B' insert into #TABLE_A select '002','小明','0619','事件C' insert into #TABLE_B select '001','0615',11.11 insert into #TABLE_B select '002','0617',9.00 insert into #TABLE_B select '001','0617',22.22 insert into #TABLE_B select '002','0618',7.00 insert into #TABLE_B select '002','0619',5.00 -- 取得「小於等於事件日之前」所有記錄(依 TABLE_B.date 由大到小做排名) ;with tb1 as ( select t1.id as id1,t1.eve_d,t2.id as id2,t2.date,t2.score, row_number() over (partition by t1.id,t1.eve_d order by t2.date desc) num1 from #TABLE_A t1 inner join #TABLE_B t2 on t1.id = t2.id and t1.eve_d >= t2.date ), -- 將最接近「第一日」的記錄排在第一位(num2 = 1) tb2 as ( select *,row_number() over (partition by id1,eve_d order by num1 desc) num2 from tb1 where 1 >= num1 ), -- 將最接近「第二日」的記錄排在第一位(num2 = 1) tb3 as ( select *,row_number() over (partition by id1,eve_d order by num1 desc) num2 from tb1 where 2 >= num1 ) -- 最後結果 select t1.id,t1.name,t1.eve_d,t1.event, t2.date,t2.score, t3.date as pre_date,t3.score as pre_score from #TABLE_A t1 inner join tb2 t2 on t2.num2 = 1 and t2.id1 = t1.id and t2.eve_d = t1.eve_d inner join tb3 t3 on t3.num2 = 1 and t3.id1 = t1.id and t3.eve_d = t1.eve_d /* id name eve_d event date score pre_date pre_score 001 小華 0616 事件A 0615 11.11 0615 11.11 002 小明 0618 事件B 0618 7.00 0617 9.00 002 小明 0619 事件C 0619 5.00 0618 7.00 */ ※ 引述《kalt (甘党)》之銘言： : 資料庫名稱：MSSQL 資料庫版本：2008 R2 EXPRESS : 新手求指導QQ 有兩個TABLE資料，其中A記錄玩家遭遇事件日和描述 : B則是記錄當日的得分情形，長得像這樣 : TABLE_A TABLE_B : id name eve_d event id date score : ----------------------------- ----------------------- : 001 小華 0616 事件A 001 0615 11.11 : 002 小明 0618 事件B 002 0617 9.00 : 002 小明 0619 事件C 001 0617 22.22 : 002 0618 7.00 : 002 0619 5.00 : 其中TABLE_B雖然不是每天都有分數記錄，但每個id一定在事件日之前有分數記錄。 : 而需要TABLE_A為主，去取得該事件發生日之前「最接近兩日的得分和日期」， : 組成一張新的TABLE。(若是僅有前一筆分數記錄，則兩次都以前一筆為主) : 也就是說希望得到下述的結果： : id name eve_d event date score pre_date pre_score : ------------------------------------------------------------------------ : 001 小華 0616 事件A 0615 11.11 0615 11.11 : 002 小明 0618 事件B 0618 7.00 0617 9.00 : 002 小明 0619 事件C 0619 5.00 0618 7.00 : 目前自己嘗試的想法是先將TABLE_B整理成這樣 : id date score pre_date pre_score : ---------------------------------------------- : 001 0615 11.11 0615 11.11 : 001 0617 22.22 0615 11.11 : 002 0617 9.00 0617 9.00 : 002 0618 7.00 0617 9.00 : 002 0619 5.00 0618 7.00 : 於是先把TABLE_B join自己，用很暴力的方法串出來... : SELECT id, date, score, pre_date, pre_score : FROM( : SELECT Row_Number() over (PARTITION BY a.id, b.date order by a.id) as RowNum, : a.id : ,a.date : ,a.score : ,b.date as pre_date : ,b.score as pre_score : FROM TABLE_B a : LEFT JOIN TABLE_B b : ON a.id = b.id : WHERE b.date < a.date ) a : WHERE a.RowNum = '1' --這個是JOIN自己後把前一日丟到Pre_date和pre_score的情形 : UNION : SELECT a.id, min(a.date) as date, a.score : ,min(a.date) as pre_date, a.score as pre_score : FROM TABLE_B a JOIN : (SELECT : id, : date = min(date) : FROM TABLE_B : GROUP BY id) AS b : ON a.id = b.id AND a.date = b.date : GROUP BY a.id, a.score --這個是當date是最早一筆的時候，直接存進pre的情況 : 嗯，然後再拿這個新的TABLE C和TABLE_A JOIN...再限定事件日等於最接近的date : .........然後我就卡關了Q__Q : 請教各位先進有什麼方法可以解決卡關的地方， : 或者是整個觀念太差應該砍掉重練，也請各方高手不吝指教m(_ _)m -- ※ 編輯: cutekid (61.221.80.36), 06/22/2016 14:52:44