// 更新“Count”列，使其具有最高重復行 ID 的值，洗掉 //基于“Url”列的重復行（只留下 ID 最低的行），
| ID | First_Name | Count | Url |
| -- | ---------- | ----- | ---------- |
| 1 | A | 10 | www.A.com |
| 2 | B | 21 | www.B.com |
| 3 | C | 12 | www.C.com |
| 4 | D | 31 | www.D.com |
| 5 | A | 13 | www.A.com |
| 6 | D | 18 | www.D.com |
| 7 | A | 5 | www.A.com |
EXPECTED RESULT
| ID | First_Name | Count | Url |
| -- | ---------- | ----- | --------- |
| 1 | A | 5 | www.A.com |
| 2 | B | 21 | www.B.com |
| 3 | C | 12 | www.C.com |
| 4 | D | 18 | www.D.com |
uj5u.com熱心網友回復：
由于您沒有包括這是什么型別的資料庫，所以我不得不猜測一下。但您可以在 SQL Server Management Studio v17.9.1 Fiddle 中選擇最新的副本：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e266cfbca912dc1a541f5f474a0b018d
這就是您可以選擇最新值的方式，并且使用此邏輯，您可以簡單地更新 First_Name 匹配的值，并洗掉其余的值等。
CREATE TABLE #testValues (
ID int NOT NULL
,[First_Name] varchar(255) NOT NULL
,[Count] int NULL
,[Url] varchar(255) NOT NULL
)
INSERT INTO #testvalues (
ID
,[First_Name]
,[Count]
,[Url]
)
VALUES (1,'A',10,'www.A.com'),
(2,'B',21,'www.B.com'),
(3,'C',12,'www.C.com'),
(4,'D',31,'www.D.com'),
(5,'A',13,'www.A.com'),
(6,'D',18,'www.D.com'),
(7,'A',5,'www.A.com');
select min(a.id) as 'ID', a.First_Name
into #lowestId
from #testValues a
group by a.first_name
SELECT a.*
into #newValues
from #testValues a
left join #testValues b
on a.First_Name=b.First_Name and a.Url=b.Url and a.id < b.ID
where b.ID is null
order by a.First_Name
select case when b.ID is null then a.ID else b.ID end as 'ID',
a.count,
a.first_name,
a.url
from #newValues a
left join #lowestId b
on a.first_name=b.first_name and b.ID < a.ID
order by case when b.ID is null then a.ID else b.ID end
drop table #lowestId
drop table #newValues
drop table #testValues
uj5u.com熱心網友回復：
另一個不改變表結構的：
UPDATE your_table yt SET yt.count = (SELECT max(yt2.id) FROM your_table yt2 WHERE yt2.Url = yt.Url AND yt.id != yt2.id);
DELETE FROM your_table yt WHERE yt.Url IN (SELECT yt2.Url from your_table WHERE yt2.id < yt.id)
uj5u.com熱心網友回復：
我想你想按 url 洗掉雙精度值組，所以你可以試試這個：
DELETE t FROM your_table t
INNER JOIN (
SELECT Url,MIN(count) AS min_count
FROM your_table
GROUP BY Url
HAVING COUNT(ID) > 1
) as t2 on t2.Url = t.Url
WHERE t.count > t2.min_count
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/457347.html