关联表，取子表某个字段最小值
表一：
ID     code
1       yi
2       er
3       san
表二：
ID       PID     startTime
1         1           2007-01-03
2         1           2005-2-2
3       2               2007-1-1
======================
问题是   ：
对两表关联：
取结果：
ID         code     startTime
1             yi         2005-2-2
2             er         2007-1-1


------解决方案--------------------
select a.id , a.code , min(b.starttime) starttime from a,b where a.id = b.code group by a.id , a.code
------解决方案--------------------
select a.ID , a.code , min(b.starttime) starttime from 表一 as a, 表二 as b where a.ID = b.PID group by a.ID, a.code

------解决方案--------------------
select
a.id, a.code, min(b.starttime) as starttime
from 表一 a, 表二 b
where a.id = b.pid
group by a.id, a.code
order by a.id
------解决方案--------------------
---方法1
Select
B.PID As ID,
A.Code,
B.StartTime
From
(Select PID,Min(StartTime) As StartTime From 表二 Group By PID) As B
Left Join
表一 As A
On B.Pid=A.ID
Order By B.PID
---方法2
Select
PID As ID,
(Select Code From 表一 Where ID=B.PID) As Code,
Min(StartTime) As StartTime
From
表二 As B
Group By PID
------解决方案--------------------
--原始数据：@T1
declare @T1 table(ID int,code varchar(4))
insert @T1
select 1, 'yi ' union all
select 2, 'er ' union all
select 3, 'san '
--原始数据：@T2
declare @T2 table(ID int,PID int,startTime datetime)
insert @T2
select 1,1, '2007-01-03 ' union all
select 2,1, '2005-2-2 ' union all
select 3,2, '2007-1-1 '

--取最小：这个符合题目要求
select a.*,startTime=min(b.startTime) from @T1 a join @T2 b on a.ID=b.PID group by a.ID,a.code order by a.ID

--取最大
select a.*,startTime=max(b.startTime) from @T1 a join @T2 b on a.ID=b.PID group by a.ID,a.code order by a.ID

--取最小：这个符合题目要求
select a.*,b.startTime from @T1 a join @T2 b on a.ID=b.PID where b.startTime=(select min(startTime) from @T2 where PID=b.PID) order by a.ID

--取最大
select a.*,b.startTime from @T1 a join @T2 b on a.ID=b.PID where b.startTime=(select max(startTime) from @T2 where PID=b.PID) order by a.ID

--取随机
select a.*,b.startTime from @T1 a join @T2 b on a.ID=b.PID where b.startTime=(select top 1 startTime from @T2 where PID=b.PID order by newid()) order by a.ID

------解决方案--------------------
declare @a table(id int identity(1,1),code varchar(20))
insert @a
select 'yi '
union all
select 'er '
union all
select 'san '

declare @b table(id int identity(1,1),pid int,starttime datetime)
insert @b
select 1, '2007-01-03 '
union all
select 1, '2005-2-2 '
union all
select 2, '2007-1-1 '

select c.id,c.code,d.starttime from @a c,