with Temp as (SELECT idd = ROW_NUMBER() OVER (ORDER BY id,value), * FROM 表test) DELETE FROM Temp WHERE idd NOT IN (SELECT MIN(idd) FROM Temp GROUP BY id,value) 这个肯定可以实现你的功能,直接用吧。
你看看这个是啥? select a.* from test a inner join (select *, row_number()over(partition by id,value order by id) as n from test) b on a.id=b.id where b.n>1
------------------------------------------------------------------------ -- Author : HappyFlyStone -- Date : 2009-06-08 14:51:51 -- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation -- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) -- -------------------------------------------------------------------------- Test Data: test IF OBJECT_ID('[test]') IS NOT NULL DROP TABLE [test] Go CREATE TABLE test([id] INT,[value] NVARCHAR(1)) Go INSERT INTO test SELECT 1,'a' UNION ALL SELECT 1,'a' UNION ALL SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 2,'b' UNION ALL SELECT 2,'b' GO --Start ;with cte as( select *,rowid = row_number() over(partition by id order by id) from test ) delete from cte where rowid > 1select * from test--Result: /*id value ----------- ----- 1 a 2 b(2 行受影响)*/ --End
原因是你的代码子查询中包含了重复了这两条数据 id value 1 a 2 b 但是没有保留一条,而是直接删除掉了,就是说把1 a的直接全删了,2 b的也是,看看我的代码就知道区别在哪儿了,我的保留了一条的。
with Temp as
(SELECT idd = ROW_NUMBER() OVER (ORDER BY id,value), *
FROM 表test)
DELETE FROM Temp
WHERE idd NOT IN
(SELECT MIN(idd) FROM Temp
GROUP BY id,value) 这个肯定可以实现你的功能,直接用吧。
你看看这个是啥?
select a.* from test a
inner join
(select *, row_number()over(partition by id,value order by id) as n from test) b
on a.id=b.id
where b.n>1
-- Author : HappyFlyStone
-- Date : 2009-06-08 14:51:51
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: test
IF OBJECT_ID('[test]') IS NOT NULL
DROP TABLE [test]
Go
CREATE TABLE test([id] INT,[value] NVARCHAR(1))
Go
INSERT INTO test
SELECT 1,'a' UNION ALL
SELECT 1,'a' UNION ALL
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 2,'b'
GO
--Start
;with cte
as(
select *,rowid = row_number() over(partition by id order by id)
from test
)
delete from cte where rowid > 1select * from test--Result:
/*id value
----------- -----
1 a
2 b(2 行受影响)*/
--End
id value
1 a
2 b
但是没有保留一条,而是直接删除掉了,就是说把1 a的直接全删了,2 b的也是,看看我的代码就知道区别在哪儿了,我的保留了一条的。