关联表，取子表某个字段最小值

[Almzg_0]

表一：
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

[tomyuansir]

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 行）
*/

[Almzg_0]

要得sql语句，谢谢

[dawugui]

select a.id , a.code , min(b.starttime) starttime from a,b where a.id = b.code group by a.id , a.code

[dobear_0922]

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

[dawugui]

create table a(ID int, code varchar(10))
insert into a values(1, 'yi')
insert into a values(2, 'er')
insert into a values(3, 'san')
create table b(ID int, PID int, startTime datetime)
insert into b values(1, 1, '2007-01-03')
insert into b values(2, 1, '2005-2-2')
insert into b values(3, 2 , '2007-1-1')
go

select a.id , a.code , min(b.starttime) starttime from a,b where a.id = b.pid group by a.id , a.code order by a.id

drop table a,b

/*
id code starttime
----------- ---------- -----------------------
1 yi 2005-02-02 00:00:00.000
2 er 2007-01-01 00:00:00.000

（所影响的行数为 2 行）
*/

[dawugui]

第一个写错了.应该是a.id = b.pid

[kelph]

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

[SoftwKLC]

---方法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

[SoftwKLC]

---创建测试环境
Create Table 表一(ID int, code varchar(10))
Insert 表一 Select 1, 'yi'
Union All Select 2, 'er'
Union All Select 3, 'san'
Create Table 表二(ID int,PID int,StartTime datetime)
Insert 表二 Select 1, 1, '2007-01-03'
Union All Select 2, 1, '2005-2-2'
Union All Select 3, 2, '2007-1-1'
Go
---查询结果
---方法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
Order By PID

---清除测试环境
Drop Table 表一,表二
---结果
/*
ID Code StartTime
----------- ---------- ----------------------------
1 yi 2005-02-02 00:00:00.000
2 er 2007-01-01 00:00:00.000

（所影响的行数为 2 行）

ID Code StartTime
----------- ---------- ---------------------------
1 yi 2005-02-02 00:00:00.000
2 er 2007-01-01 00:00:00.000

（所影响的行数为 2 行）

*/

[Limpire]

--原始数据：@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