--sql2005+有效
;WITH CTE AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY code ORDER BY id)RN
FROMTB
)
DELETE FROM CTE WHERE RN>1--SQL2000有效
DELETE FROM TB
WHERE EXISTS(SELECT 1 FROM TB T WHERE TB.code=T.code AND TB.id>T.id)
;WITH CTE AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY code ORDER BY id)RN
FROMTB
)
DELETE FROM CTE WHERE RN>1--SQL2000有效
DELETE FROM TB
WHERE EXISTS(SELECT 1 FROM TB T WHERE TB.code=T.code AND TB.id>T.id)
SELECT * FROM dbo.wf_vou WHERE descr LIKE 'PPV%' ORDER BY re
acc_noa LIKE '65%' AND credit>200000 AND cur='rmb' AND voucher_no LIKE 'jv15%'CREATE TABLE #tb (_id INT ,_NO int,_NAME CHAR(8))
INSERT #tb
SELECT 1, 2,'张山' UNION ALL
SELECT 2, 2,'张山' UNION ALL
SELECT 3, 3,'王五' UNION ALL
SELECT 4, 4,'招六' UNION ALL
SELECT 5, 4,'招六' UNION ALL
SELECT 6, 4,'招六'SELECT * FROM #tb;WITH a1 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY _no ORDER BY _id) re
FROM #tb
)
DELETE a1 WHERE re>1SELECT * FROM #tb
create table table1
(id int,code int,name varchar(10))insert into table1
select 1,2,'张山' union all
select 2,2,'张山' union all
select 3,3,'王五' union all
select 4,4,'招六' union all
select 5,4,'招六' union all
select 6,4,'招六'
delete t
from
(select *,row_number() over(partition by code order by id) 'rn'
from table1) t
where rn>1
select * from table1/*
id code name
----------- ----------- ----------
1 2 张山
3 3 王五
4 4 招六(3 row(s) affected)
*/
DELETE FROM TB
WHERE EXISTS(SELECT 1 FROM TB T WHERE TB.code=T.code AND TB.id>T.id)这个你试过?把你改的贴上来看下
(id int,code int,name varchar(10))insert into table1
select 1,2,'张山' union all
select 2,2,'张山' union all
select 3,3,'王五' union all
select 4,4,'招六' union all
select 5,4,'招六' union all
select 6,4,'招六'
delete a
from table1 a
inner join
(select code,name,min(id) 'id'
from table1
group by code,name
having count(1)>1) c on a.code=c.code and a.name=c.name
where a.id<>c.id
select * from table1/*
id code name
----------- ----------- ----------
1 2 张山
3 3 王五
4 4 招六(3 row(s) affected)
*/