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
12 个解决方案
#1
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)
#2
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)
#3
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)
#4
select
*
from
t1 a ,t2 b
where
a.id=t2.id2
and
id2=(select min(id2) from t2 where name=b.name )
#5
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)
#6
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)
#7
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 行)
*/
#8
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
#9
一个简单的SQL,高手云集呀,顺便up。。。。。。。。。
#10
--------------------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 行受影响)
*/
#11
好多种写法啊,不过我最喜欢的 可能也是速度最快的
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)
#12
不简单啊 比如 我现在要 查询出
等级 Userlevel=16级的 那这个条件 要怎么加入
或者 查询 等级是32级的呢
等级 Userlevel=16级的 那这个条件 要怎么加入
或者 查询 等级是32级的呢
#1
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)
#2
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)
#3
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)
#4
select
*
from
t1 a ,t2 b
where
a.id=t2.id2
and
id2=(select min(id2) from t2 where name=b.name )
#5
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)
#6
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)
#7
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 行)
*/
#8
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
#9
一个简单的SQL,高手云集呀,顺便up。。。。。。。。。
#10
--------------------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 行受影响)
*/
#11
好多种写法啊,不过我最喜欢的 可能也是速度最快的
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)
#12
不简单啊 比如 我现在要 查询出
等级 Userlevel=16级的 那这个条件 要怎么加入
或者 查询 等级是32级的呢
等级 Userlevel=16级的 那这个条件 要怎么加入
或者 查询 等级是32级的呢