最近面试遇到的题目，
虽然看似简单，但是在日常工作中其实用到的也很多。
提供给大家。
IF OBJECT_ID('card') IS NOT NULL
DROP TABLE card
CREATE TABLE card
(
cardNO VARCHAR(8),
cardDate date,
cardState CHAR(1),--卡片状态 1正常 2注销
cardType VARCHAR(1)--卡片状态 1身份证 2居住证
)
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 card(cardNO,cardDate,cardState,cardType)
VALUES
('001','2014-06-18',1,1),
('005','2014-06-18',1,2),
('002','2014-06-18',1,1),
('003','2014-06-18',1,1),
('004','2014-06-18',1,1),
('006','2014-06-18',1,1),
('007','2014-06-18',1,1),
('008','2014-06-18',1,2)
INSERT cardDetail(cardNO,NAME,idCard,cardState)
VALUES
('001','张三','31010',1),
('005','张三','31010',1),
('002','李四','31011',1),
('003','王五','31012',1),
('006','张三','31010',0),
('003','王五','31012',1),
('007','欧阳','31013',1),
('008','欧阳','31013',1)
SELECT * FROM card AS c
SELECT * FROM cardDetail AS cd
--1.
SELECT carddate FROM card c
INNER JOIN cardDetail AS cd ON c.cardNO=cd.cardNO
WHERE cd.idCard='31010'
--2.
SELECT idcard,COUNT(1) FROM cardDetail GROUP BY idCard HAVING COUNT(1)>2
--3.
UPDATE cardDetail SET cardState =0 WHERE idCard='31010'
UPDATE card SET cardState =0
FROM card AS c
INNER JOIN cardDetail AS cd ON c.cardNO=cd.cardNO
WHERE cd.idCard='31010'
--4.
DELETE FROM cardDetail
WHERE NAME='欧阳'
and EXISTS(SELECT 1 FROM card WHERE cardno=cardDetail.cardNO AND cardType=2
)
--5.
--单个字段 idcard
DELETE FROM cardDetail
WHERE idcard IN(SELECT idcard FROM cardDetail GROUP BY idCard HAVING COUNT(1)>1)
AND id NOT IN (SELECT MIN(id) FROM cardDetail GROUP BY idCard HAVING COUNT(1)>1)
------解决方案--------------------
5.
DELETE tA FROM cardDetail as tA inner join
(SELECT ROW_NUMBER()over(partition by idCard order by id) as A,id FROM cardDetail )
as tB on tA.id=tB.id
where tB.A > 1