Question

求一条SQL语句：主表左联子表，只取对应子表第一条记录

求一条SQL语句：主表左联子表，只取对应子表第一条记录
例如：
主表：TABLE1(number,FIELD11,FIELD12)
子表：TABLE2(number,father,FIELD21,FIELD22)
主表与子表的关联：TABLE2.father=TABLE1.number
TABLE1中的数据为
number FIELD11 FIELD12
1 F111 F121
2 F112 F122

TABLE2中的数据为：
number father FIELD21 FIELD22
1 1 F211 F221
2 1 F212 F222
3 1 F213 F223
4 2 F214 F224
5 2 F215 F225
6 2 F216 F226

查询结果为：
FIELD11 FIELD12 FIELD21 FIELD22
F111 F121 F211 F221
F112 F122 F214 F224

（子表可以按照number排序取第一条）
还忘赐教。

Answer 1

1.
select FIELD11,FIELD12,FIELD21,FIELD22 from table1
left join
(select father,field21,field22 from (select min(number)number from table2 group by father) a
left join
table2
on a.number=table2.number) b
on table1.number = b.father

2.
select FIELD11,FIELD12,FIELD21,FIELD22 from table1
left join
table2
on table1.number = table2.father
where table2.number in (select min(number) from table2 group by father)

Answer 2

select FIELD11,FIELD12,FIELD21,FIELD22
from TABLE1 A,TABLE2 B
WHERE B.father=A.number
GROUP BY FIELD11,FIELD12,FIELD21,FIELD22

Answer 3

用outer apply
select t1.id,tt2.id from t1
outer apply (
select top 1 id from t2
where t2.外键id=t1.外键id
order by t2.排序字段

)tt2