求SQL数据库删除重复语句 我有个数据表，表名是souk,内容为列分sid sn sgn smf ad da其中sid内容为唯一值

Question

da为入库日期 求当sn&sgn同时重复时只保留da最新的一条数据，其他重复部分全部删除 sid sn sgn smf ad da 1212 app jap E:\AK E:\AK 2011.02 1314 ban jap E:\AK E:\AK 2012.01 1501 app ua E:\Au E:\Au 2012.03 16820 app jap E:\AK E:\AK 2012.09 结果为 sid sn sgn smf ad da 1314 ban jap E:\AK E:\AK 2012.01 1501 app ua E:\AK E:\AK 2012.03 16820 app jap E:\AK E:\AK 2012.09

Answer 1

CREATE TABLE #souk (
sid INT,
sn VARCHAR(5),
sgn VARCHAR(5),
smf VARCHAR(10),
ad VARCHAR(10),
da varchar(10)
);

INSERT INTO #souk
SELECT 1212, 'app', 'jap', 'E:\AK', 'E:\AK', '2011.02' UNION ALL
SELECT 1314, 'ban', 'jap', 'E:\AK', 'E:\AK', '2012.01' UNION ALL
SELECT 1501, 'app', 'ua', 'E:\Au', 'E:\Au', '2012.03' UNION ALL
SELECT 16820, 'app', 'jap', 'E:\AK', 'E:\AK', '2012.09' UNION ALL
SELECT 9999, 'ban', 'ua', 'E:\AK', 'E:\AK', '2012.03' UNION ALL
SELECT 1202021,'ban', 'jap', 'E:\AK', 'E:\AK', '2012.05';

DELETE #souk
WHERE
(sn + sgn + da) NOT IN
(SELECT sn + sgn + MAX(da) FROM #souk GROUP BY sn, sgn);

1> select * from #souk
2> go
sid sn sgn smf ad da
----------- ----- ----- ---------- ---------- ----------
1501 app ua E:\Au E:\Au 2012.03
16820 app jap E:\AK E:\AK 2012.09
9999 ban ua E:\AK E:\AK 2012.03
1202021 ban jap E:\AK E:\AK 2012.05

(4 行受影响)

SQL Server 2008 Express 版本下测试通过.

Answer 2

delete souk
from souk
left (
select sn,sgn,max(da) as da from souk) as a1 on a1.sn=souk.sn and a1.sgn = souk.sgn
where souk.da <> a1.da