@@@关于两表关联的问题@@@

select B1.A_ID as A_ID,B1.ID as B_ID

from B B1 where not exists (select 1 from B B1 where B1.A_ID=B2.A_ID and B1.ID>B2.ID)

create table A(ID int)  

insert into a values(1)

insert into a values(2)

insert into a values(3)

insert into a values(4)

insert into a values(5)

insert into a values(6)

insert into a values(7)

create table B(ID int,   A_ID int)

insert into B values(1 ,     1)

insert into B values(2 ,     7) 

insert into B values(3 ,     6) 

insert into B values(4 ,     2) 

insert into B values(5 ,     2) 

insert into B values(6 ,     5) 

insert into B values(7 ,     1) 

insert into B values(8 ,     7) 

insert into B values(9 ,     2)

go



select a.id a_id , min(b.id) b_id from a , b where a.id = b.a_id group by a.id



drop table A , b 



/*

a_id        b_id        

----------- ----------- 

1           1

2           4

5           6

6           3

7           2



（所影响的行数为 5 行）

*/

select a.id a_id , min(b.id) b_id from a , b where a.id = b.a_id group by a.id