sql 多个字段分组，删除重复记录，保留ID最小的一条

3r211

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正常 2注销
)
  INSERT cardDetail(cardNO,NAME,idCard,cardState)
VALUES
('001','张三','31010',1),
('005','张三','31010',1),
('002','李四','31011',1),
('003','王五','31012',1),
('006','张三','31010',0),
('004','王五','31012',1),
('007','欧阳','31013',1),
('008','欧阳','31013',1)



--SQL编写要求
--根据idcard,cardstate分组后删除重复的,使其保留id最小的记录

--最终结果为：
(1，'001','张三','31010',1),
(4，'003','王五','31012',1),
(7，'007','欧阳','31013',1),

解决


with t as
(
   select id,cardNO,NAME,idCard,cardState,
      row_number() over (partition by idcard,cardstate order by id) as od
   from cardDetail
)
select * from t where od = 1;