sql语句帮忙，两表关联 取另一个表中存在的任意一条记录

[wyq29]

tb1：

id name
1 a
2 b
3 c
4 s
5 d
6 e


tb2

id name title
1 a aa
2 a ab
3 b bb
4 b ba
5 b bbb
6 d dd
7 f ddd
8 c cc

id都是自动的 两个name关联 得出tb1中的name在tb2中存在的任意一条记录

id name title
1 a aa
3 b bb
6 d dd
8 c cc

[jinboychen]

select a.id from tb1 a right join tb2 b on a.id=b.id

[jinboychen]

看错了
select a.id from tb1 a right join tb2 b on a.name=b.name

[FancyBoy247]

select min(id) as id, name, min(title) as title from tb2 where name in (select name from tb1) group by name order by id
-------------------------------Result:-------------------
id name title
1 a aa
3 b bb
6 d dd
8 c cc

[中国风]

select 	
	b.*
from 
(select 
	* ,[ID2]=(select top 1 ID from tb2 where Name=t.Name order by newID())
from 
	tb1 t )a
join
	tb2 b
	on a.Name=b.Name and a.ID2=b.ID

[中国风]

取任意一个，每次查询的结果都在变
go

create table #1(id int,   name nvarchar(2))
insert #1 select 1,     'a' 
insert #1 select 2,     'b' 
insert #1 select 3,     'c' 
insert #1 select 4,     's' 
insert #1 select 5,     'd' 
insert #1 select 6,     'e' 

go
create table #2(id int,    name nvarchar(2),    title nvarchar(5))
insert #2 select 1,       'a',             'aa' 
insert #2 select 2,       'a',             'ab' 
insert #2 select 3,       'b',             'bb' 
insert #2 select 4,       'b',             'ba' 
insert #2 select 5,       'b',             'bbb' 
insert #2 select 6,       'd',             'dd' 
insert #2 select 7,       'f',             'ddd' 
insert #2 select 8,       'c',             'cc' 
go

select     
    b.*
from 
(select 
    * ,[ID2]=(select top 1 ID from #2 where Name=t.Name order by newID())
from 
    #1 t )a
join
    #2 b
    on a.Name=b.Name and a.ID2=b.ID


id          name title 
----------- ---- ----- 
1           a    aa
4           b    ba
6           d    dd
8           c    cc

（所影响的行数为 4 行）

[cxmcxm]

select * from tb2 a where exists(select * from tb1 where name=a.name)
	and not exists(select * from tb2 where name=a.name and id>a.id)