34,838
社区成员




--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([id] int,[name] varchar(2))
insert [tb1]
select 1,'李' union all
select 2,'王' union all
select 3,'张'
select * from [tb1]
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([p_id] varchar(2),[file] varchar(4),[id] int)
insert [tb2]
select 'a1','aaaa',1 union all
select 'b1','bbbb',1 union all
select 'c1','cccc',2 union all
select 'd1','dddd',2 union all
select 'e1','eeee',3
select * from [tb2]
select A.id,A.name,B.[file],B.p_id
from tb1 A
inner join tb2 B on A.id = B.id and B.[file] = (select min(tb2.[file]) as N from Tb2 where id = B.id)
--结果
/*id name file p_id
1 李 aaaa a1
2 王 cccc c1
3 张 eeee e1*/
SELECT A.*,T.[file],T.p_id FROM A,B T
WHERE A.ID=B.ID AND
T.[file]=(SELECT MIN([file]) FROM B WHERE ID=T.ID )