table_A 0001 0002 table_B 0001 KK 0001 CC 0002 DD ----------------------- 效果 0001 KK 0002 DD 不能用 top 1
if object_id('table_A') is not null drop table table_A go if object_id('table_B')is not null drop table table_B go create table table_A ( id int ) create table table_B ( id int, B nvarchar(10) ) insert into table_A select 0001 union all select 0002 go insert into table_B select 0001,'KK' union all select 0001,'CC' union all select 0002,'DD' select a.id,b.B from table_A a join (select id,max(B) B from table_B group by id) b on a.id=b.id
select * from Table_1
0001
0002 table_B
0001 KK
0001 CC
0002 DD -----------------------
效果
0001 KK
0002 DD
不能用 top 1
if object_id('table_A') is not null
drop table table_A
go
if object_id('table_B')is not null
drop table table_B
go
create table table_A
( id int )
create table table_B
( id int, B nvarchar(10) )
insert into table_A select 0001
union all select 0002
go
insert into table_B select 0001,'KK'
union all select 0001,'CC'
union all select 0002,'DD'
select a.id,b.B from table_A a join
(select id,max(B) B from table_B group by id) b
on a.id=b.id