现在有两个表,
表ab
id,a,b
1,11,111
2,11,222
3,11,333
4,11,444
5,22,111
6,22,222
表cd
id,c,d
1,11,123
2,22,123
3,33,123
两表建立关联,要求输出结果
11,111
22,111
表ab
id,a,b
1,11,111
2,11,222
3,11,333
4,11,444
5,22,111
6,22,222
表cd
id,c,d
1,11,123
2,22,123
3,33,123
两表建立关联,要求输出结果
11,111
22,111
from
(select a,min(b) as b
from 表ab
group by a) t1
inner join 表cd t2
on t1.a=t2.c
抱歉,刚才看错题意
*/declare @ab table (id int,a int,b int)
insert @ab
select 1,11,111 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,111 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123select a.a, a.b
from
@ab a join @cd b
on a.a = b.c
where a.b in (select top 1 b from @ab where a=a.a)
/*
11 111
22 111
*/
抱歉,刚才看错题意
*/declare @ab table (id int,a int,b int)
insert @ab
select 1,11,111 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,111 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123select a.a, a.b
from
@ab a join @cd b
on a.a = b.c
--如果要取最小一条,用min(b),如果按记录顺序取第一条,用top 1 b
where a.b = (select min(b) from @ab where a=a.a)
/*
11,111
22,111
*/
select cd.c,a.b
from (select a,min(b) b from ab group by a) a,
cd
where a.a=cd.c
order by cd.c
(select a , min(b) b from ab group by a) t1
left join cd t2
on t1.a = t2.a
insert into ab values(1,11,111)
insert into ab values(2,11,222)
insert into ab values(3,11,333)
insert into ab values(4,11,444)
insert into ab values(5,22,111)
insert into ab values(6,22,222)
create table cd(id int,a int,b int)
insert into cd values(1,11,123)
insert into cd values(2,22,123)
insert into cd values(3,33,123)
select t1.* from
(select a , min(b) b from ab group by a) t1
left join cd t2
on t1.a = t2.a
drop table ab,cd/*
a b
----------- -----------
11 111
22 111
(所影响的行数为 2 行)
*/
insert into ab values(1,11,111)
insert into ab values(2,11,222)
insert into ab values(3,11,333)
insert into ab values(4,11,444)
insert into ab values(5,22,111)
insert into ab values(6,22,222)
create table cd(id int,a int,b int)
insert into cd values(1,11,123)
insert into cd values(2,22,123)
insert into cd values(3,33,123)
goselect ab.a , min(ab.b) b
from ab,cd
where ab.a = cd.a
group by ab.adrop table ab,cd/*
a b
----------- -----------
11 111
22 111
(所影响的行数为 2 行)
*/
是要输出表ab中a列与表CD中c列相同的b列的第一条记录!而不是最小的记录。
insert @ab
select 1,11,999 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,888 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123
--这是第一条
select a.a, a.b
from
@ab a join @cd b
on a.a = b.c
where a.b = (select top 1 b from @ab where a=a.a)
/*
11,999
22,999
*/
insert @ab
select 1,11,999 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,888 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123
--这是第一条
select a.a, a.b
from
@ab a join @cd b
on a.a = b.c
where a.b = (select top 1 b from @ab where a=a.a)
/*
11,999
22,888
*/--这是最小一条
select a.a, a.b
from
@ab a join @cd b
on a.a = b.c
where a.b = (select min(b) from @ab where a=a.a)
/*
11,222
22,222
*/
from ab left join cd on ab.a=cd.c
from ab left join cd on ab.a=cd.c
是要输出表ab中a列与表CD中c列相同的b列的第一条记录!而不是最小的记录。
drop table #ab
create table #ab(id int,a int,b int)
insert #ab
select 1,11,111 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,111 union all
select 6,22,222
create table #cd(id int,c int,d int)
insert #cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123select t1.a,t1.b from
(
select * from #ab where id in(select min(id) from #ab group by a)
)t1 join #cd b on t1.a=b.ca b
----------- -----------
11 111
22 111(2 row(s) affected)
insert @ab
select 1,11,111 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,111 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123--查询结果
select aa.a,aa.b from @ab aa inner join @cd cc on aa.a=cc.c
where not exists(select a,b from @ab where a=aa.a and id<aa.id)
--非常感谢各位的帮助,可都不是我想要的结果!是要输出表ab中a列与表CD中c列相同的b列的第一条记录!而不是最小的记录。create table ab(id int,a int,b int)
insert into ab values(1,11,111)
insert into ab values(2,11,222)
insert into ab values(3,11,333)
insert into ab values(4,11,444)
insert into ab values(5,22,111)
insert into ab values(6,22,222)
create table cd(id int,a int,b int)
insert into cd values(1,11,123)
insert into cd values(2,22,123)
insert into cd values(3,33,123)
goselect t1.* from ab t1,
(
select ab.a , min(ab.id) id
from ab,cd
where ab.a = cd.a
group by ab.a
) t2
where t1.a = t2.a and t1.id = t2.id
drop table ab,cd/*
id a b
----------- ----------- -----------
1 11 111
5 22 111
(所影响的行数为 2 行)
*/
insert @ab
select 1,11,111 union all
select 2,11,222 union all
select 3,11,333 union all
select 4,11,444 union all
select 5,22,111 union all
select 6,22,222
declare @cd table (id int,c int,d int)
insert @cd
select 1,11,123 union all
select 2,22,123 union all
select 3,33,123--这个是针对你的ID是不重复的 并且是由小到大进行增长的select aa.a,aa.b from @ab aa inner join @cd cc on aa.a=cc.c
where not exists(select a,b from @ab where a=aa.a and id<aa.id)
--这个是如果没有固定不重复的列 创建一个临时表进行代替原表@ab
select identity(int,1,1) bh ,aa.* into #ab from @ab aa
--查询
select aa.a,aa.b from #ab aa inner join @cd cc on aa.a=cc.c
where not exists(select a,b from #ab where a=aa.a and bh<aa.bh)
select top 5 url,record_id,ObjectName from Images i join uptown u on i.record_id=u.id where p_id=11 and c_id=4 and i.image_name='外景图' and u.vip>0 and u.turnover=0 order by vip
在uptown表中,只有个id 字段,值是唯一的,在images中有多个与之对应的图片,要求,一个id只对应一个图片,显示出前五条记录.
from (select *
from ab
where id in (
select min(ID)
from ab
group by a ) ) a
inner join cd b on a.a = b.C