作者cutekid (可愛小孩子)
看板Database
標題Re: [SQL ] join on 條件為「最接近的值」？
時間Wed Jun 22 14:50:51 2016
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