高手请进，组合查询时筛选记录的问题

吉普赛的歌 2012-03-30 02:11:58

-- 建立初始数据

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 种可能， 写存储过程要一一照顾到， 真是有点勉为其难了。 

--哪位大侠指点一下优化的思路， 感激不尽

...全文

112 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌 2012-03-30

打赏
举报

谢谢各位!

q107770540 2012-03-30

打赏
举报

SELECT top 1 p.* FROM Person p,

(select  PersonName,IdCard from Person group by PersonName,IdCard having count(1)>1) q

where p.PersonName = q.PersonName and p.IdCard = q.IdCard

dawugui 2012-03-30

打赏
举报

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                                         IdCard                                             PersonGender Birthday                                               

----------- -------------------------------------------------- -------------------------------------------------- ------------ ------------------------------------------------------ 

1           a                                                  123456789012345678                                 1            1970-01-02 00:00:00.000

3           a                                                  123456789012345678                                 1            1970-01-02 00:00:00.000



（所影响的行数为 2 行）

*/



drop table Person