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;
------解决方案--------------------
写出来了？------解决方案--------------------
根据SQL编写要求,所得结果应该是id: 1 3 4 5 7.
与LZ提供的最终结果不一样喔.
------解决方案--------------------
+1