两张表数据关联,第一张表为主表,第二章表为辅表,主表为一,辅表为多TableA
id
1
2
3
TableB
id ParentID
1 1
2 2
3 2
4 3
5 3
6 3
我想要的效果是
ID ParentID
1 1
2 2
3 4当辅表为多条数据时,只查询一条数据,请教这个语句该如何写呀?
id
1
2
3
TableB
id ParentID
1 1
2 2
3 2
4 3
5 3
6 3
我想要的效果是
ID ParentID
1 1
2 2
3 4当辅表为多条数据时,只查询一条数据,请教这个语句该如何写呀?
这样写,tableB的所有列都展示出来了啊@!如果你还要展示tableA的,那:
select a.*,b.* from TableA a inner join tableB b on a.id=b.parentid where not exists(select 1 from TableB where ParentID = b.ParentID and id<b.id)
create table TableA(id int)insert into TableA
select 1 union all
select 2 union all
select 3create table TableB(id int,ParentID int)insert into TableB
select 1, 1 union all
select 2, 2 union all
select 3, 2 union all
select 4, 3 union all
select 5, 3 union all
select 6, 3
select a.id,b.id 'ParentID'
from TableA a
inner join
(select row_number() over(partition by ParentID order by getdate()) rn,
id,ParentID from TableB) b
on a.id=b.ParentID and b.rn=1id ParentID
----------- -----------
1 1
2 2
3 4(3 row(s) affected)