SQL只取一条记录

moss1229 2008-07-10 10:01:48



select PCcode,PCFuName,PCShName,PCWholeName,

PCType from v_bk_PortCity order by PCWholeName

结果如下:

PCcode   PCFuName   PCShName   PCWholeName   PCType

368	  AAL	     奥勒松	Aalesund	P

651	  AAL	     奥勒松	Aalesund	C

322	  AAR	     奥胡斯	Aarhus	        P

606	  AAR	     奥胡斯	Aarhus	        C

232	  ABR	     阿伯丁	Aberdeen	C

256	  ABR	     阿伯丁	Aberdeen	P

987	  AED	     阿伯丁	Aberdeen	C

241	  ABI	     Abilene	Abilene	        C

431	  AUH	     阿布扎比	Abu Dhabi	P

1294	  AUH	     阿布扎比	Abu Dhabi	C



想要的结果:

PCcode   PCFuName   PCShName   PCWholeName   PCType

368	  AAL	     奥勒松	Aalesund	P

322	  AAR	     奥胡斯	Aarhus	        P

232	  ABR	     阿伯丁	Aberdeen	C

987	  AED	     阿伯丁	Aberdeen	C

241	  ABI	     Abilene	Abilene	        C

431	  AUH	     阿布扎比	Abu Dhabi	P



就是只要PCFuName   PCShName   相同的只取一条记录

...全文

1155 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

zzyman 2008-07-10

打赏
举报

select * from v_bk_PortCity where pccode in(select min(pccode) from v_bk_PortCity group by PCFuName,PCShName)

zhiguo2008 2008-07-10

打赏
举报

select * from v_bk_portCity where pccode=(select max(pccode) from v_bk_portCity A where a.pcfuname =v_bk_portCity.pcfuname and a.pcshname=v_bk_portCity.pcshname)

中国风 2008-07-10

打赏
举报

 set nocount on ;

if not object_id('Tempdb..#T') is null

	drop table #T

Go

Create table #T([PCcode] int,[PCFuName] nvarchar(3),[PCShName] nvarchar(7),[PCWholeName] nvarchar(9),[PCType] nvarchar(1))

Insert #T

select 368,N'AAL',N'奥勒松',N'Aalesund',N'P' union all

select 651,N'AAL',N'奥勒松',N'Aalesund',N'C' union all

select 322,N'AAR',N'奥胡斯',N'Aarhus',N'P' union all

select 606,N'AAR',N'奥胡斯',N'Aarhus',N'C' union all

select 232,N'ABR',N'阿伯丁',N'Aberdeen',N'C' union all

select 256,N'ABR',N'阿伯丁',N'Aberdeen',N'P' union all

select 987,N'AED',N'阿伯丁',N'Aberdeen',N'C' union all

select 241,N'ABI',N'Abilene',N'Abilene',N'C' union all

select 431,N'AUH',N'阿布扎比',N'Abu Dhabi',N'P' union all

select 1294,N'AUH',N'阿布扎比',N'Abu Dhabi',N'C'

Go

select [PCcode],[PCFuName],[PCShName],[PCWholeName],[PCType]

from 

(Select *,row=row_number()over(partition by [PCFuName],[PCShName] order by [PCcode] ) from #T)T

where row=1

order by PCWholeName





PCcode      PCFuName PCShName PCWholeName PCType

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

368         AAL      奥勒松      Aalesund    P

322         AAR      奥胡斯      Aarhus      P

232         ABR      阿伯丁      Aberdeen    C

987         AED      阿伯丁      Aberdeen    C

241         ABI      Abilene  Abilene     C

431         AUH      阿布扎比     Abu Dhabi   P

中国风 2008-07-10

打赏
举报

处理表重复记录(查询和删除)_整理贴4
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

arrow_gx 2008-07-10

打赏
举报

select * from tb1 where pccode in(select min(pccode) from tb1 group by PCFuName,PCShName)

昵称被占用了 2008-07-10

打赏
举报

select * from v_bk_PortCity a

where not exists (

select 1 from v_bk_PortCity 

where PCFuName=a.PCFuName and PCShName=a.PCShName   

and PCcode<a.PCcode

)