现在有两个表,
表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,222
表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,222
抱歉,刚才看错题意
*/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
*/
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请楼上不要在同一时间一题多问
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,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)