请教一sql问题，谢谢！

[zzc205]

两张表如下：
表一： 表二:
ID1 ID2 Year
1 1 2003
2 1 2004

以上两表通过'ID'关联，表一对表二为一对多关系，现要求选出ID1 = ID2且Year值为该列最大值的记录行，若表一与表二没有可关联的记录，则显示表一的列，表二的列显示为空。
例如上表的选择结果应该是
ID1 ID2 Year
1 1 2004
2 null null
求实现上述功能的sql语句，谢谢!

[tomhuang]

select * from table1 a,
(select id2,max(year) from table2 group by id2) b
where a.id1 *= b.id2

[lalakid]

SELECT 表一.ID1,
表二.ID2,
表二.year
FROM 表一 LEFT JOIN (

SELECT ID2,
Year = MAX(CONVERT( INT,year))
FROM 表二
GROUP BY ID2,year) 表二
ON 表一.ID1 = 表二.ID2
GO

[zjcxc]

--如果就这几个字段,则这样:
select *
from 表一 a
left join 表二 b
on a.id1=b.id2
and not exists(select * from 表二 where id2=b.id2 and [year]>b.[year])

[qizhanfeng]

select A.ID1,max(B.ID2),max(B.Year) from 表一 A left join 表二 B on A.ID1=B.ID2
group by A.ID1

[lsxaa]

select a.ID1,b.ID2,max(b.Year) as Year
from table1 a left join table2 b on a.ID1=b.ID2
group by a.ID1,b.ID2

[zjcxc]

--错了,上面的可以显示表一,表二的所有字段

--另一种方法是:
select a.*,b.*
from 表一 a
left join 表二 b on a.id1=b.id2
left join (select id2,[year]=max([year]) from 表二 group by id2) b1 on b.id2=b1.id2 and b.[year]=b1.[year]

[lalakid]

SELECT 表一.ID1,
表二.ID2,
表二.year
FROM 表一 LEFT JOIN (

SELECT ID2,
Year = MAX(CONVERT( INT,year))
FROM 表二
GROUP BY ID2) 表二
ON 表一.ID1 = 表二.ID2
GO

[lalakid]

简单问题,大家都来抢分啊
挖哈哈^

[szh3210]

SELECT dbo.t1.id1, b.id2, b.[year]
FROM dbo.t1 LEFT OUTER JOIN
(SELECT a.id2, a.[year]
FROM t2 a INNER JOIN
(SELECT id2 AS id2, MAX([year]) AS [year]
FROM t2
GROUP BY id2) b ON a.id2 = b.id2 AND a.[year] = b.[year]) b ON
dbo.t1.id1 = b.id2