作者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