关联表，取子表某个字段最小值
表一：
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,
(select * from @b a where not exists(select 1 from @b b where a.pid=b.pid and a.id <b.id)) d
where c.id=d.pid
/*
（所影响的行数为 3 行）
（所影响的行数为 3 行）
id code starttime
----------- -------------------- ------------------------------------------------------
1 yi 2005-02-02 00:00:00.000
2 er 2007-01-01 00:00:00.000
（所影响的行数为 2 行）
*/
