-- 建立初始数据
CREATE TABLE Person
(
    PersonId INT IDENTITY(1,1) PRIMARY KEY,
    PersonName NVARCHAR(50),
    IdCard NVARCHAR(50),
    PersonGender NCHAR(2),
    Birthday DATETIME DEFAULT GETDATE()
)

INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345677',1,'1970-1-3')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('b','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('b','123456789012345677',2,'1970-1-3') 
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('c','123456789012345676',1,'1970-1-3')


-- 查找 同名而且同身份证号的 记录
SELECT *
FROM  Person a
WHERE  
      a.PersonName IN (SELECT c1.PersonName
                            FROM   Person c1
                            GROUP BY
                                   c1.PersonName
                            HAVING COUNT(*) > 1)
      AND a.IdCard IN (SELECT c2.IdCard
                            FROM   Person c2
                            GROUP BY
                                   c2.IdCard
                            HAVING COUNT(*) > 1)
                            
/*  结果不符合要求---并没有找出同名而且同身份证号的记录
PersonId    PersonName    IdCard    PersonGender    Birthday
1    a    123456789012345678    1     1970-01-02 00:00:00.000
2    a    123456789012345677    1     1970-01-03 00:00:00.000
3    a    123456789012345678    1     1970-01-02 00:00:00.000
4    b    123456789012345678    1     1970-01-02 00:00:00.000
5    b    123456789012345677    2     1970-01-03 00:00:00.000
*/

--也想过用笨办法，用存储过程，将同名的记录， 按PersonName排序放在临时表， 
--再遍历每一条记录，如果同名的， 则依次比较IdCard， 相同则作标记
--可是， 要求并没有这么简单， 上面的例子都只是我简化过的， 
--实际是有4个checkbox, 要求 □ 姓名,  □ 性别,  □ 身份证号,  □ 生日
--选中之后能组合查询， 这样算下来 4*4 = 16 种可能， 写存储过程要一一照顾到， 真是有点勉为其难了。 
--哪位大侠指点一下优化的思路， 感激不尽

CREATE TABLE Person
(
    PersonId INT IDENTITY(1,1) PRIMARY KEY,
    PersonName NVARCHAR(50),
    IdCard NVARCHAR(50),
    PersonGender NCHAR(2),
    Birthday DATETIME DEFAULT GETDATE()
)

INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345677',1,'1970-1-3')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('a','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('b','123456789012345678',1,'1970-1-2')
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('b','123456789012345677',2,'1970-1-3') 
INSERT INTO Person (PersonName,IdCard,PersonGender,Birthday) VALUES('c','123456789012345676',1,'1970-1-3')

--1、只查姓名和身份证号码
select personname , idcard from Person group by personname , idcard having count(1) > 1
/*
personname                                         idcard                                             
-------------------------------------------------- -------------------------------------------------- 
a                                                  123456789012345678

（所影响的行数为 1 行）
*/

--2、查所有字段的内容
select m.* from person m where exists(select 1 from (select personname , idcard from Person group by personname , idcard having count(1) > 1) n where n.personname = m.personname and n.idcard = m.idcard)
/*
PersonId    PersonName