求一条sql语句

--result

attachid    name       aid         attachid    content    

----------- ---------- ----------- ----------- ---------- 

100         北京         5           100         dsd

200         上海         1           200         test



（所影响的行数为 2 行）

--max(aid)

select m.* , n.* from table1 m,

(

  select t.* from table2 t where aid = (select max(aid) from table2 where attchid = t.attchid)

) n

where m.attachid = n.attachid



--min(aid)

select m.* , n.* from table1 m,

(

  select t.* from table2 t where aid = (select min(aid) from table2 where attchid = t.attchid)

) n

where m.attachid = n.attachid

declare @a table (attachid int,name varchar(10))

insert into @a select 100,'北京'

insert into @a select 200,'上海'



declare @b table (aid int,attachid varchar(10),content varchar(10))

insert into @b select 1,200,'test'

insert into @b select 3,100,'sdfsdf'

insert into @b select 4,100,'3212'

insert into @b select 5,100,'dsd'



select * from @a a

left join @b b on a.attachid=b.attachid

where not exists(

select 1 from @b where attachid=b.attachid and aid>b.aid

)

create table A(

attachid int,

[name] nvarchar(10)

)

insert A select 100,'北京'   

insert A select 200,'上海' 



create table B(

aid int,

attachid int,

content nvarchar(10)   

)

insert B select 1,200,'test'   

insert B select 3,100,'sdfsdf'   

insert B select 4,100,'3212'   

insert B select 5,100,'dsd' 



select * from A

left join (

select * from B as tmp

where not exists(select * from B where attachid=tmp.attachid and aid>tmp.aid)

)B on A.attachid=B.attachid

select   distinct table1.*   from   table1   left   join   table2   on   table1.attachid=   table2.attachid 

select a.name,b.aid,b.attachid,b.content

from table1 a left join table2 b

 on a.attachid=b.attachid

group by a.name,b.aid,b.attachid,b.content