请问有两表
t1 t2
id name id year glid
1 w 1 2000 1
2 e 2 2001 1
3 t 3 2002 1
4 1999 2
5 2000 2
6 2001 3
7 2002 3t1 ,t2用id和glid进行关联
如何能实现查询出结果
name year
w 2000
e 1999
t 2001就是只取每个关联的年度最小的记录。谢谢!
t1 t2
id name id year glid
1 w 1 2000 1
2 e 2 2001 1
3 t 3 2002 1
4 1999 2
5 2000 2
6 2001 3
7 2002 3t1 ,t2用id和glid进行关联
如何能实现查询出结果
name year
w 2000
e 1999
t 2001就是只取每个关联的年度最小的记录。谢谢!
a.name,min(b.[year]) as [year]
from
t1 a,t2 b
where
a.id=b.glid
group by
a.name
from t1 a
inner join t2 b
on a.id=b.glid
group by a.name
insert into @t select 1 , 'w'
union all select 2 , 'e'
union all select 3 , 't' declare @a table(id int,[year] int,glid int)
insert into @a select 1,2000,1 union all select
2,2001,1
union all select 3,2002,1
union all select 4 , 1999 , 2
union all select 5 , 2000 , 2
union all select 6 , 2001 , 3
union all select 7 , 2002 , 3select a.name,min(b.[year]) as [year] from @t a,@a b where a.id=b.glid group by a.name
insert into t1
select 1,'w' union all
select 2,'e' union all
select 3,'t'
go
create table t2(id int,year int ,gid int)
insert into t2
select 1,2000,1 union all
select 2,2001,1 union all
select 3,2002,1 union all
select 4,1999,2 union all
select 5,2000,2 union all
select 6,2001,3 union all
select 7,2002,3
goselect t1.name,t2.year
from t1,t2
where t1.id=t2.gid
and t2.year=(select min(year) from t2 where t2.gid=t1.id)
insert into t1 select 1,'w'
union all select 2,'e'
union all select 3,'t'
create table t2(id int,[year] nvarchar(4),glid int)
insert into t2 select 1,'2000',1
union all select 2,'2001',1
union all select 3,'2002',1
union all select 4,'1999',2
union all select 5,'2000',2
union all select 6,'2001',3
union all select 7,'2002',3
select * from t1
select * from t2select t1.name,min(t2.year) from t1,t2
where t1.id=t2.glid
group by t1.namedrop table t1
drop table t2results:
----------------------
e 1999
t 2001
w 2000