34,838
社区成员




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