27,582
社区成员
发帖
与我相关
我的任务
分享[表关联]相差最小值表关联
表A
ID name
7 X1
5 X1
4 X2
3 X3
1 XX
-------------------------------
表B
DC TIX
8 C0
5 C1
3 C3
1 C4
-------------------------------
结果: 也就是,A表所有记录,A的ID与B的DC差值大于0的最少值列关联
ID name DC TIX
7 X1 5 C1
5 X1 3 C3
4 X2 3 C3
3 X3 1 C4
1 XX null null
-----------------------
我现在想到的方法是先A关联B表a.ID>B.DC先关联,再保留ID相同差值最少的数据...有没有更好的方法呢? ID,DC是唯一不会重复的...
ID name
7 X1
5 X1
4 X2
3 X3
1 XX
-------------------------------
表B
DC TIX
8 C0
5 C1
3 C3
1 C4
-------------------------------
结果: 也就是,A表所有记录,A的ID与B的DC差值大于0的最少值列关联
ID name DC TIX
7 X1 5 C1
5 X1 3 C3
4 X2 3 C3
3 X3 1 C4
1 XX null null
-----------------------
我现在想到的方法是先A关联B表a.ID>B.DC先关联,再保留ID相同差值最少的数据...有没有更好的方法呢? ID,DC是唯一不会重复的...
...全文
请发表友善的回复…
发表回复
wtujedp 2015-07-10
- 打赏
- 举报
with t1 as
(
-- ID name
select 7 ID, 'X1' name
union all
select 5, 'X1' name union all
select 4, 'X2' name union all
select 3, 'X3' name union all
select 1, 'XX' name
)
,t2 as
(
select 8 DC , 'C0' TIX
union all
select 5, 'C1' union all
select 3, 'C3' union all
select 1, 'C4'
)
select *,(select top 1 dc from t2 where t1.ID>t2.DC) dc,(select top 1 TIX from t2 where t1.ID>t2.DC) TIX from t1
Pact_Alice 2015-07-10
- 打赏
- 举报
--数据源
;with t1 as
(
-- ID name
select 7 ID, 'X1' name union all
select 5, 'X1' name union all
select 4, 'X2' name union all
select 3, 'X3' name union all
select 1, 'XX' name
),t2 as(
select 8 DC ,'C0' TIX union all
select 5, 'C1' union all
select 3, 'C3' union all
select 1, 'C4'
),cet AS (
--根据链接查询,然后在进行分组排序
SELECT
t1.ID,
t1.name,
t2.DC,
t2.TIX,
ROW_NUMBER()OVER(PARTITION BY ID,NAME ORDER BY DC)AS Number
FROM t1
LEFT JOIN t2 ON t1.ID>t2.DC
)
--我们取最大的一个值
SELECT
ID,
NAME,
DC,
TIX
FROM cet c2
WHERE Number =(SELECT MAX(Number) FROM cet c1 WHERE c1.ID=c2.ID)ORDER BY ID
肥胖的柠檬 2015-07-10
- 打赏
- 举报
.........大哥们, 能换个方法不,这种例子明显列不只是2例的了
Pact_Alice 2015-07-10
- 打赏
- 举报
楼上的方法很好很好
--数据源
;with t1 as
(
-- ID name
select 7 ID, 'X1' name union all
select 5, 'X1' name union all
select 4, 'X2' name union all
select 3, 'X3' name union all
select 1, 'XX' name
),t2 as(
select 8 DC ,'C0' TIX union all
select 5, 'C1' union all
select 3, 'C3' union all
select 1, 'C4'
)
--取T1表中大于T2表中的第一个数据
select *,(select top 1 dc from t2 where t1.ID>t2.DC) dc,(select top 1 TIX from t2 where t1.ID>t2.DC) TIX from t1
