取A表数据,关联B表任意一条数据

时间:2023-03-08 16:23:54
 表A===================
AID, AName

1 jack

2 mary
3 lily

表B==================

BID, AID, BName

1 1 aaa
2 1 bbb
3 1 ccc
4 2 ddd
5 2 eee

一、sql语句==============

--A 表数据

select  * from A_TB
--B 表数据
select  * from B_TB
--结果:取A表数据,关联B表任意一条数据
select  * from A_TB
left join (select MAX(BID)bid,MAX(BName)bname,AID from B_TB group by AID)b on A_TB.AID=b.AID

二、结果==================

AID ,  AName, Bid,BName

1 jack 3 ccc 1
2 mary 5 eee 2
3 lily NULL NULL NULL

另外一种方法======

select * from (
select a.*,(select top 1 Id from B as b where b.CgId =a.ID)as bid
from A as a) as temp
left join B as b2 on temp.bid=b2.Id