sqlserver的left join只获取其中一条
时间: 2023-10-13 09:04:44 浏览: 68
在SQL Server中,使用LEFT JOIN只获取其中一条记录的方法有多种。以下是其中一种方法的示例代码:
```
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 <> ''
```
这个查询语句使用了子查询和ROW_NUMBER()函数来对表B进行分区并按照指定字段进行降序排序。然后,通过LEFT JOIN将表A和子查询的结果进行连接,并使用WHERE子句筛选出rownum为1且id不为空的记录。这样就可以只获取其中一条符合条件的记录。\[1\]
#### 引用[.reference_title]
- *1* [SqlServer Left Join 右表有多条数据,根据条件筛选一条](https://blog.csdn.net/yi_zongjishi/article/details/124588801)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item]
- *2* *3* [工作遇到的sql问题:left join 最新一条数据](https://blog.csdn.net/qq_37700773/article/details/116246026)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]