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

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

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

 ('','张三','',1),

 ('','张三','',1),

 ('','李四','',1),

 ('','王五','',1),

 ('','张三','',0),

 ('','王五','',1),

 ('','欧阳','',1),

 ('','欧阳','',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;