SQL根据多字段删除重复项，其结果只以ID最新（大）保留一个条记录

[qq_37728490]

IF OBJECT_ID('cardDetail') IS NOT NULL 
  DROP TABLE cardDetail
 CREATE TABLE cardDetail
 (
 id INT IDENTITY(1,1) PRIMARY KEY,
 cardNO VARCHAR(8),
 NAME VARCHAR(30),   
 idCard VARCHAR(18),
 cardState CHAR(1)--卡片状态 1合格 0不合格
 )
  INSERT cardDetail(cardNO,NAME,idCard,cardState)
 VALUES
 ('001','张三','31010',0),
 ('001','张三','31010',1),
 ('002','李四','31011',0),
 ('003','王五','31019',1),
 ('001','张三','31014',0),
 ('003','王五','31012',1),
 ('004','欧阳','31013',0),
 ('004','欧阳','31013',1),
 ('001','张三','31014',0),
 ('001','张三','31014',1),
 ('002','李四','31011',1),
 ('003','王五','31016',1),
 ('001','张三','31015',0),
 ('003','王五','31012',1),
 ('004','欧阳','31017',1),
 ('004','欧阳','31018',0)  

--SQL编写要求
--根据cardNO,idcard为标识，删除重复项，保留一条id最大的记录

--最终结果为：
(2，'001','张三','31010',1),
(11，'002','李四','31011',1),
(4，'003','王五','31019',1),
(10，'001','张三','31014',1),
(12，'003','王五','31016',1),
(13，001','张三','31015',0),
(14，'003','王五','31012',1),
(15，'004','欧阳','31017',1),
(16，'004','欧阳','31018',0)

[卖水果的net]

 delete from cardDetail 
 where id < any (select id from cardDetail t where cardNO = cardDetail.cardNO and idcard = cardDetail.idcard)

[唐诗三百首]

delete a
 from cardDetail a
 where exists(select 1 
              from cardDetail b
              where b.cardNO=a.cardNO and b.idcard=a.idcard 
              and b.id>a.id)


select * from dbo.cardDetail

/*
id          cardNO   NAME       idCard     cardState
----------- -------- ---------- ---------- ---------
2           001      张三         31010      1
4           003      王五         31019      1
8           004      欧阳         31013      1
10          001      张三         31014      1
11          002      李四         31011      1
12          003      王五         31016      1
13          001      张三         31015      0
14          003      王五         31012      1
15          004      欧阳         31017      1
16          004      欧阳         31018      0

(10 row(s) affected)
*/

[卖水果的net]

 with m as (
 select row_number() over(partition by cardNO ,idcard order by id desc) rn ,* from cardDetail
 )
 delete m where rn > 1 
 go

[shoppo0505]

with data as( select *, _index = ROW_NUMBER()over(partition by name, idcard order by id desc, name ) from carddetail ) select * from data where _index = 1 不过比你多了id=8的数据，应该说是你的少了吧。

[二月十六]

语句：

;WITH tempa AS ( SELECT   cardNO ,						--获取重复项
                        idCard ,
                        COUNT(1) AS num
               FROM     cardDetail
               GROUP BY cardNO ,
                        idCard
               HAVING   COUNT(1) > 1
             )
,tempb AS (												--获取重复项中最大id
SELECT  cardDetail.cardNO ,
        cardDetail.idCard ,
        MAX(id) AS id
FROM    cardDetail
        JOIN tempa ON tempa.cardNO = cardDetail.cardNO
                      AND tempa.idCard = cardDetail.idCard
        GROUP BY cardDetail.cardNO ,
                        cardDetail.idCard
)
DELETE  cardDetail										--删除操作
FROM    cardDetail ,
        tempa
WHERE   cardDetail.cardNO = tempa.cardNO
        AND cardDetail.idCard = tempa.idCard
        AND id NOT IN ( SELECT  Id
                        FROM    tempb )

						SELECT * FROM dbo.cardDetail

结果：

[qq_37728490]

引用 2 楼 ap0405140 的回复:

delete a
 from cardDetail a
 where exists(select 1 
              from cardDetail b
              where b.cardNO=a.cardNO and b.idcard=a.idcard 
              and b.id>a.id)


select * from dbo.cardDetail

/*
id          cardNO   NAME       idCard     cardState
----------- -------- ---------- ---------- ---------
2           001      张三         31010      1
4           003      王五         31019      1
8           004      欧阳         31013      1
10          001      张三         31014      1
11          002      李四         31011      1
12          003      王五         31016      1
13          001      张三         31015      0
14          003      王五         31012      1
15          004      欧阳         31017      1
16          004      欧阳         31018      0

(10 row(s) affected)
*/

谢谢