如何join时如果多条只取一条

[newlive1001]

主表 t1
id name sex
1 张三 男
2 李四 女

从表
id2 name PicUrl
1 张三 http://www.aa.com/1.jpg
2 张三 http://www.aa.com/2.jpg
3 李四 http://www.aa.com/3.jpg
4 李四 http://www.aa.com/4.jpg


我要取出
id name sex id2 PicUrl
1 张三 男 1 http://www.aa.com/1.jpg
2 李四 女 3 http://www.aa.com/3.jpg

[luxi0194]

一个简单的SQL，高手云集呀，顺便up。。。。。。。。。

[喜-喜]

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------

use test
go
if object_id('test.dbo.t1') is not null drop table t1
-- 创建数据表
create table t1
(
id int,
name char(5),
sex char(3)
)
go
--插入测试数据
insert into t1 select 1,'张三','男'
union all select 2,'李四','女'
go
if object_id('test.dbo.t2') is not null drop table t2
-- 创建数据表
create table t2
(
id2 int,
name char(5),
PicUrl char(30)
)
go
--插入测试数据
insert into t2 select 1,'张三','http://www.aa.com/1.jpg'
union all select 2,'张三','http://www.aa.com/2.jpg'
union all select 3,'李四','http://www.aa.com/3.jpg'
union all select 4,'李四','http://www.aa.com/4.jpg'
go
--代码实现
select id,a.name,a.sex,b.id2,PicUrl from t1 a inner join (
select idd=row_number()over(partition by name order by getdate()),* from t2 )b
on a.name=b.name where idd=1 order by id
/*测试结果

id  name  sex   id2 PicUrl
----------------------------------------------------
1	张三  男 	1	http://www.aa.com/1.jpg       
2	李四  女 	3	http://www.aa.com/3.jpg       

(2 行受影响)
*/

[chuifengde]

select * from t1 a inner join t2 b on a.id=t2.id2
where not exists(select 1 from t2 where name=b.name and id2<b.id2)

[水族杰纶]

select a.*,
       b.id2,
       b.PicUrl
from t1 a ,t2 b
where a.name=b.name
and b.PicUrl=(select min(PicUrl) from t2 where name=b.name)

[dawugui]

select t1.* , t.id2 , t.PicUrl from t1 , t where t1.name = t.name and t.id2 = (select min(id2) from t2 where name = t.name)

select t1.* , t.id2 , t.PicUrl from t1 , t where t1.name = t.name and not exists (select 1 from t2 where name = t.name and id2 < t.id2)

[--小F--]

select
 * 
from
 t1 a ,t2 b 
where
 a.id=t2.id2
and
 id2=(select min(id2) from t2 where name=b.name )

[永生天地]

select a.id,a.nam,a.sex,b.picurl from t1 a left join t2 b on a.name=b.name
where b.id2 = (select top 1 c.id2 from t2 c where c.name=b.name order by c.id2)

[htl258_Tony]

select * from t1 a inner join t2 b on a.id=t2.id2
where id2 = (select top 1 id2 from t2 where name=b.name)

[dawugui]

create table t1(id int,name varchar(10),sex varchar(10))
insert into t1 values(1, '张三', '男')
insert into t1 values(2, '李四', '女')
create table t2(id2 int,name varchar(10) , PicUrl varchar(60))
insert into t2 values(1 ,'张三', 'http://www.aa.com/1.jpg')
insert into t2 values(2 ,'张三', 'http://www.aa.com/2.jpg')
insert into t2 values(3 ,'李四', 'http://www.aa.com/3.jpg')
insert into t2 values(4 ,'李四', 'http://www.aa.com/4.jpg')
go

select t1.* , t.id2 , t.PicUrl from t1 , t2 t where t1.name = t.name and t.id2 = (select min(id2) from t2 where name = t.name) order by t1.id

select t1.* , t.id2 , t.PicUrl from t1 , t2 t where t1.name = t.name and not exists (select 1 from t2 where name = t.name and id2 < t.id2)  order by t1.id


drop table t1 , t2

/*
id          name       sex        id2         PicUrl                                                       
----------- ---------- ---------- ----------- ------------------------------------------------------------ 
1           张三         男          1           http://www.aa.com/1.jpg
2           李四         女          3           http://www.aa.com/3.jpg

（所影响的行数为 2 行）

id          name       sex        id2         PicUrl                                                       
----------- ---------- ---------- ----------- ------------------------------------------------------------ 
1           张三         男          1           http://www.aa.com/1.jpg
2           李四         女          3           http://www.aa.com/3.jpg

（所影响的行数为 2 行）

*/

[永生天地]

select a.id,a.nam,a.sex,b.picurl from t1 a left join (select * from t2 m where not exists(select 1 from t2 n where n.name=m.name and n.id2<m.id2)) b on a.name=b.name

[newlive1001]

好多种写法啊,不过我最喜欢的 可能也是速度最快的

select * from t1 a inner join t2 b on a.name=b.name
where b.id2 = (select top 1 id2 from t2 where name=b.name)

[kkkand123]

不简单啊　比如 　我现在要 查询出
等级 Userlevel=16级的 那这个条件 要怎么加入
或者　查询　等级是３２级的呢