求一条sql语句,两个表之间的关联,在线等
现在有两个表,
表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
[解决办法]
--前面我已经说啦，取最小就用min(b)，按顺序取第一条就用top 1。
declare @ab table (id int,a int,b int)
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
*/
[解决办法]
-- lengjing126() ( ) 信誉：100 2007-09-03 08:28:05 得分: 0
--非常感谢各位的帮助，可都不是我想要的结果！是要输出表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)
go
select 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 行）
*/
[解决办法]
selecta.*
from(select *
from ab
where id in (
select min(ID)
from ab
groupby a ) ) a
innerjoincd b on a.a = b.C
