SQLServer 表连接时使用top 1 去除重复数据

left join SM_SOLine soline on soline.SO=so.ID and soline.DocLineNo=(select MAX(DocLineNo) from SM_SOLine where so=so.ID)

 

 

 

create table #test8
(
  id int,
  name varchar(50)
)

drop table #test9
create table #test9
(
  id int,
  name varchar(50)
)

insert into #test8
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'ddd' 

insert into #test9
select 3,'abc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'eee' union all
select 2,'ccc' union all
select 1,'ghd' union all
select 7,'hgd' 

 

select A.id,A.name,B.name from #test8 A
inner join #test9 B on A.ID=B.ID
where not exists(select 1 from #test9 where id=B.id and name>B.name)

此处B表的Name字段必须是在B表中不重复的某字段，不然，会出现重复结果

 

 

 

 

 

 

use TestDB
drop table test8
create table test8
(
      id int,
      name1 varchar(50),
      name2 varchar(50),
      name3 varchar(50)
)

 

drop table test9
create table test9
(
      id int,
      name1 varchar(50),
      name2 varchar(50),
      name3 varchar(50)

)

insert into test8 --ItemMaster
select  1,'a','aaa','111' union all
select  2,'b','bbb','222' union all
select  3,'c','ccc','333' union all
select  4,'d','ddd','444' union all
select  5,'e','eee','555' union all
select  6,'f','fff','666' union all
select  7,'g','ggg','777' union all
select  8,'h','hhh','888'

 

insert into test9 --Gprice
select  1,'a','aaa','567' union all
select  2,'a','aaa','567' union all
select  3,'i','ccc','641' union all
select  4,'c','ccc','981' union all
select  5,'e','eee','126' union all
select  6,'f','fff','873' union all
select  7,'c','ccc','946' union all
select  8,'h','ddd','767'

--insert into test9 --Gprice
--select  1,'a','aaa','A' union all
--select  2,'a','aaa','A' union all
--select  3,'i','ccc','B' union all
--select  4,'c','ccc','C' union all
--select  5,'e','eee','D' union all
--select  6,'f','fff','E' union all
--select  7,'c','ccc','F' union all
--select  8,'h','ddd','G'

/*
  1,a  --
  3,c  --
  5,e
  6,f
*/

此写法会导致test8中的ID重复

select A.id,A.name1,A.name3,B.name2,B.name3 B_name3 from test8 A
inner join test9 B on A.name1=B.name1 and A.name2=B.name2

 使用此方法可以解决问题：

select A.id,A.name1,A.name3,B.name2,MAX(B.name3) B_name3  
from test8 A
inner join test9 B on A.name1=B.name1 and A.name2=B.name2
where not exists(select 1 from test9 where id=B.id and name3>B.name3)
group by A.id,A.name1,A.name3,B.name2

 

PS. B表里的name3可以是test8表里的除关键字段以外任意一个前提是有值的字段，

      可以是整型，也可是字符串，都可显示正常，如下列结果