update a set lat=(select min(lat) from tb where name=a.name) from tb a
declare @t table (Name varchar(3),Long numeric(8,5),Lat numeric(6,4)) insert into @t select 'A地',100.02948,50.5258 union all select 'A地',100.02948,50.5259update @t set Lat=b.Lat from @t a left join (select Name,Long,max(Lat) as Lat from @t group by Name,Long) b on a.Name=b.Name and a.Long=b.Longselect * from @t /* Name Long Lat ---- --------------------------------------- --------------------------------------- A地 100.02948 50.5259 A地 100.02948 50.5259 */ --想更新成另外一个就max改成min
谢谢大家,解决了,特别感谢ssp2009,maco_wang!
UPDATE A SET A.Lat = B.Lat --按Name分组,把每个组中的Lot更新成第一个出现的Lot FROM tb A INNER JOIN --按Name分组,找到每组中的第一个出现的Lot ( SELECT a.Name, b.Lat FROM (SELECT DISTINCT [Name] FROM tb) a CROSS APPLY (SELECT TOP(1) Lat FROM tb WHERE [Name]=a.[Name]) b ) B ON A.Name = B.Name AND A.Lat <> B.Lat
from tb a
declare @t table (Name varchar(3),Long numeric(8,5),Lat numeric(6,4))
insert into @t
select 'A地',100.02948,50.5258 union all
select 'A地',100.02948,50.5259update @t set Lat=b.Lat from @t a left join
(select Name,Long,max(Lat) as Lat from @t group by Name,Long)
b on a.Name=b.Name and a.Long=b.Longselect * from @t
/*
Name Long Lat
---- --------------------------------------- ---------------------------------------
A地 100.02948 50.5259
A地 100.02948 50.5259
*/
--想更新成另外一个就max改成min
SET A.Lat = B.Lat --按Name分组,把每个组中的Lot更新成第一个出现的Lot
FROM tb A
INNER JOIN --按Name分组,找到每组中的第一个出现的Lot
(
SELECT a.Name, b.Lat FROM
(SELECT DISTINCT [Name] FROM tb) a
CROSS APPLY
(SELECT TOP(1) Lat FROM tb WHERE [Name]=a.[Name]) b
) B
ON A.Name = B.Name AND A.Lat <> B.Lat