表之间的关联问题.
现在有两个表,
表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,123
select 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)
/*
11111
22111
*/
[解决办法]
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)
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 行）
*/
