SELECT 表一.ID1, 表二.ID2, 表二.year FROM 表一 LEFT JOIN ( SELECT ID2, Year = MAX(CONVERT( INT,year)) FROM 表二 GROUP BY ID2,year) 表二 ON 表一.ID1 = 表二.ID2 GO
--如果就这几个字段,则这样: select * from 表一 a left join 表二 b on a.id1=b.id2 and not exists(select * from 表二 where id2=b.id2 and [year]>b.[year])
select A.ID1,max(B.ID2),max(B.Year) from 表一 A left join 表二 B on A.ID1=B.ID2 group by A.ID1
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
--错了,上面的可以显示表一,表二的所有字段--另一种方法是: 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]
SELECT 表一.ID1, 表二.ID2, 表二.year FROM 表一 LEFT JOIN ( SELECT ID2, Year = MAX(CONVERT( INT,year)) FROM 表二 GROUP BY ID2) 表二 ON 表一.ID1 = 表二.ID2 GO
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
表二.ID2,
表二.year
FROM 表一 LEFT JOIN ( SELECT ID2,
Year = MAX(CONVERT( INT,year))
FROM 表二
GROUP BY ID2,year) 表二
ON 表一.ID1 = 表二.ID2
GO
select *
from 表一 a
left join 表二 b
on a.id1=b.id2
and not exists(select * from 表二 where id2=b.id2 and [year]>b.[year])
group by A.ID1
from table1 a left join table2 b on a.ID1=b.ID2
group by a.ID1,b.ID2
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]
表二.ID2,
表二.year
FROM 表一 LEFT JOIN ( SELECT ID2,
Year = MAX(CONVERT( INT,year))
FROM 表二
GROUP BY ID2) 表二
ON 表一.ID1 = 表二.ID2
GO
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