两张表数据关联, 辅表只列出一条数据?

[chkmouse]

两张表数据关联,第一张表为主表，第二章表为辅表，主表为一，辅表为多

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



当辅表为多条数据时，只查询一条数据，请教这个语句该如何写呀？

[SSXYC]

select * from TableB a where id = (select top 1 id from TableB where a.ParentID = ParentID)

[chkmouse]

[Quote=引用 1 楼 ssxyc 的回复:]
select * from TableB a where id = (select top 1 id from TableB where a.ParentID = ParentID)
[/Quote]


可是TableB的某些字段需要作为查询条件呀,而且TableB的一些字段也需要展示出来

[-晴天]

select * from TableB a where not exists(select 1 from TableB where ParentID = a.ParentID and id<a.id)

楼主,Pid 为 3 ,对应的 ID 没有 3!

[-晴天]

[Quote=引用 2 楼 chkmouse 的回复:]

引用 1 楼 ssxyc 的回复:
select * from TableB a where id = (select top 1 id from TableB where a.ParentID = ParentID)



可是TableB的某些字段需要作为查询条件呀,而且TableB的一些字段也需要展示出来
[/Quote]

这样写,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 3

create 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=1

id          ParentID
----------- -----------
1           1
2           2
3           4

(3 row(s) affected)