两张表数据关联, 辅表只列出一条数据?
两张表数据关联,第一张表为主表，第二章表为辅表，主表为一，辅表为多

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



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




[解决办法]
select * from TableB a where id = (select top 1 id from TableB where a.ParentID = ParentID)
[解决办法]

SQL code

select * from TableB a where not exists(select 1 from TableB where ParentID = a.ParentID and id<a.id)
[解决办法]
探讨


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



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

[解决办法]
SQL code
create table TableA(id int)insert into TableAselect 1 union allselect 2 union allselect 3create table TableB(id int,ParentID int)insert into TableBselect 1, 1 union allselect 2, 2 union allselect 3, 2 union allselect 4, 3 union allselect 5, 3 union allselect 6, 3select a.id,b.id 'ParentID'from TableA ainner join(select row_number() over(partition by ParentID order by getdate()) rn,id,ParentID from TableB) bon a.id=b.ParentID and b.rn=1id          ParentID----------- -----------1           12           23           4(3 row(s) affected)