IF object_id('Carddetail') is not NULL DROP TABLECarddetailCREATE TABLECarddetail (IDINT IDENTITY(1,1)PRIMARY KEY, CardnoVARCHAR(8), NAMEVARCHAR( -), IdcardVARCHAR( -), CardstateCHAR(1)--card Status 1 Normal 2 logoff ) INSERTCarddetail (cardno,name,idcard,cardstate)VALUES ('001','Zhang San','31010',1), ('005','Zhang San','31010',1), ('002','John Doe','31011',1), ('003','Harry','31012',1), ('006','Zhang San','31010',0), ('004','Harry','31012',1), ('007','Ouyang','31013',1), ('008','Ouyang','31013',1)
--sql Writing requirements
--delete duplicates based on Idcard,cardstate, keeping the record with the smallest ID
-The end result is:
(1, ' 001 ', ' Zhang San ', ' 31010 ', 1),
(4, ' 003 ', ' Harry ', ' 31012 ', 1),
(7, ' 007 ', ' Ouyang ', ' 31013 ', 1),
Solve
with as ( Select id,cardno,name,idcard,cardstate, over byOrder by as od from carddetail)Select* fromwhere=1;
SQL multiple fields grouped, delete duplicate records, keep one of the smallest IDs