， 组合查询时筛选记录的有关问题

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

SQL code

-- 建立初始数据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 aWHERE        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    Birthday1    a    123456789012345678    1     1970-01-02 00:00:00.0002    a    123456789012345677    1     1970-01-03 00:00:00.0003    a    123456789012345678    1     1970-01-02 00:00:00.0004    b    123456789012345678    1     1970-01-02 00:00:00.0005    b    123456789012345677    2     1970-01-03 00:00:00.000*/--也想过用笨办法，用存储过程，将同名的记录， 按PersonName排序放在临时表， --再遍历每一条记录，如果同名的， 则依次比较IdCard， 相同则作标记--可是， 要求并没有这么简单， 上面的例子都只是我简化过的， --实际是有4个checkbox, 要求 □ 姓名,  □ 性别,  □ 身份证号,  □ 生日--选中之后能组合查询， 这样算下来 4*4 = 16 种可能， 写存储过程要一一照顾到， 真是有点勉为其难了。 --哪位大侠指点一下优化的思路， 感激不尽

[解决办法]
select * from person a where exists(select 1 from person where personname=a.personname and idcard=a.idcard and id<>a.id)
[解决办法]

SQL code

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.0003           a                                                  123456789012345678                                 1            1970-01-02 00:00:00.000（所影响的行数为 2 行）*/drop table Person 
 
[解决办法]
SQL code
SELECT top 1 p.* FROM Person p,(select  PersonName,IdCard from Person group by PersonName,IdCard having count(1)>1) qwhere p.PersonName = q.PersonName and p.IdCard = q.IdCard 

 

查看更多 下一篇