SqlServer Left Join 右表有多条数据，根据条件筛选一条_右表多条数据取一条

1. Row_Number() OVER() 函数

语法：Row_Number() OVER (partition by 分组字段 order by 排序字段 desc)

select a.*,b.*
from 表A as a
left join
(select id, rightid,Row_Number() over(partition by id order by id desc) as rownum
from 表B) as b
on a.id=b.id
where b.rownum=1 and a.id<>''

1
2
3
4
5
6
7
8

2. Outer Apply

说明：无论联接是否存在，都会从外部表和表值函数中返回数据行。这与OUTER JOIN 很类似。如果表值函数中没有行存在，则从函数中返回的列值为NULL。

Outer Apply详细说明：SQL Server中CROSS APPLY和OUTER APPLY应用

select a.*,b.*
from 表A as a
outer apply
(select top 1 * from 表B where a.id=b.id order by 排序字段 desc) as b
1
2
3
4