declare @表A table ( x int ) insert into @表A values (123),(345),(456) declare @表B table ( x int, y int ) insert into @表B values (122,11), (344,22), (346,24), (400,33)select a.x,isnull(b.y,0) from @表A a left join @表B b on b.x = a.x -1/* x y 123 11 345 22 456 0 */
考虑到表A的x和表B的x相差1,可能存在某些数据是表B比表A的大1,也可能是表A比表B的大1。select dd.x,ISNULL(ee.y,0)as y from 表A dd left join ( select x,y from ( select aa.x,bb.y,ROW_NUMBER() over(partition by aa.x order by aa.x) as rId from 表A as aa ,表B as bb where ABS(aa.x-bb.x)=1 ) cc where rId=1) ee on dd.x=ee.x
借用二楼数据 declare @表A table ( x int ) insert into @表A values (123),(345),(456) declare @表B table ( x int, y int ) insert into @表B values (122,11), (344,22), (346,24), (400,33)select x,isnull((select TOP 1 y from @表B where x=a.x-1 or x=a.x+1),0) from @表A a
上面的sql可以简化一下 select x,ISNULL(y,0)as y from ( select a.x,b.y,ROW_NUMBER() over(partition by a.x order by a.x) as rId from 表A as a left join 表B as b on ABS(a.x-b.x)=1 ) cc where rId=1
declare @表A table ( x int ) insert into @表A values (123),(345),(456)
declare @表B table ( x int, y int ) insert into @表B values (122,11), (344,22), (346,24), (400,33) select a.x,ISNULL(min(b.y),0) as y from @表A as a left join @表B as b on a.x=b.x+1 or a.x=b.x-1 group by a.x--结果 (3 行受影响)(4 行受影响) x y ----------- ----------- 123 11 345 22 456 0 警告: 聚合或其他 SET 操作消除了 Null 值。
(
x int
)
insert into @表A values
(123),(345),(456)
declare @表B table
(
x int,
y int
)
insert into @表B values
(122,11),
(344,22),
(346,24),
(400,33)select a.x,isnull(b.y,0)
from @表A a
left join @表B b on b.x = a.x -1/*
x y
123 11
345 22
456 0
*/
select x,y from (
select aa.x,bb.y,ROW_NUMBER() over(partition by aa.x order by aa.x) as rId from 表A as aa
,表B as bb where ABS(aa.x-bb.x)=1
) cc where rId=1) ee on dd.x=ee.x
declare @表A table
(
x int
)
insert into @表A values
(123),(345),(456)
declare @表B table
(
x int,
y int
)
insert into @表B values
(122,11),
(344,22),
(346,24),
(400,33)select x,isnull((select TOP 1 y from @表B where x=a.x-1 or x=a.x+1),0)
from @表A a
select x,ISNULL(y,0)as y from (
select a.x,b.y,ROW_NUMBER() over(partition by a.x order by a.x) as rId from 表A as a
left join 表B as b on ABS(a.x-b.x)=1
) cc where rId=1
declare @表A table
(
x int
)
insert into @表A values
(123),(345),(456)
declare @表B table
(
x int,
y int
)
insert into @表B values
(122,11),
(344,22),
(346,24),
(400,33)
select a.x,ISNULL(min(b.y),0) as y from @表A as a left join @表B as b
on a.x=b.x+1 or a.x=b.x-1
group by a.x--结果
(3 行受影响)(4 行受影响)
x y
----------- -----------
123 11
345 22
456 0
警告: 聚合或其他 SET 操作消除了 Null 值。