请教SQL 如何查询身份证号码相同但姓名不同的记录？

[fly4063]

大家好 AC01表里 有 身份证号相同 姓名也相同的记录 还有 身份证号相同 姓名不同的记录

我想把身份证号相同，姓名不同的记录查询出来。

aac002是身份证号， aac003 是姓名 我写了一段是这样的：

select aac002 身份证号, aac003 姓名
from ac01
where aac002 in (SELECT aac002
FROM ac01
WHERE aac031 = '1'
GROUP BY aac002
HAVING COUNT(aac002) > 1)

查询的部分结果这样的：
66190 230105197804170753 吴春波
66191 23010519780420006X 苗慧
66192 23010519780420006X 苗慧
66193 230105197804211914 陈辉
66194 230105197804211914 赵玉玺
66195 230105197804233718 杨保河
66196 230105197804233718 杨保河

我想把
66193 230105197804211914 陈辉
66194 230105197804211914 赵玉玺
这样的记录 单独查询出来 ，应该怎么修改SQL呢？

[--小F--]

你的意思是
66191 23010519780420006X 苗慧
66192 23010519780420006X 苗慧
66193 230105197804211914 陈辉
66194 230105197804211914 赵玉玺
66195 230105197804233718 杨保河
66196 230105197804233718 杨保河
这样的结果吗?

[好汉坡]

--> 测试数据: @tb
declare @tb table (id int,sfzh varchar(18),name varchar(6))
insert into @tb
select 66190,'230105197804170753','吴春波' union all
select 66191,'23010519780420006X','苗慧' union all
select 66192,'23010519780420006X','苗慧' union all
select 66193,'230105197804211914','陈辉' union all
select 66194,'230105197804211914','赵玉玺' union all
select 66195,'230105197804233718','杨保河' union all
select 66196,'230105197804233718','杨保河'

select a.* from @tb a,@tb b
where a.sfzh=b.sfzh and a.name!=b.name
 

id          sfzh               name
----------- ------------------ ------
66193       230105197804211914 陈辉
66194       230105197804211914 赵玉玺

(2 行受影响)

[让你望见影子的墙]

select s.*
from tb s join tb t on s.身份证号=t.身份证号
where s.name <>t.name

[pt1314917]

select a.* from [AC01] a ,[AC01] b where a.身份证号=b.身份证号 
and s.name <>t.name

[icelovey]

declare @tb table([id] int,[身份证号] varchar(18),[姓名] varchar(6))
insert @tb
select 66190,'230105197804170753','吴春波' union all
select 66191,'23010519780420006X','苗慧' union all
select 66192,'23010519780420006X','苗慧' union all
select 66193,'230105197804211914','陈辉' union all
select 66194,'230105197804211914','赵玉玺' union all
select 66195,'230105197804233718','杨保河' union all
select 66196,'230105197804233718','杨保河'


SELECT T1.*
FROM @TB T1 INNER JOIN @TB T2 ON T1.身份证号 = T2.身份证号 AND T1.姓名<>T2.姓名

/*
id          身份证号               姓名
----------- ------------------ ------
66193       230105197804211914 陈辉
66194       230105197804211914 赵玉玺

(2 row(s) affected)

[--小F--]

;with f as
(select aac002 身份证号, aac003 姓名 
  from ac01 
where aac002 in (SELECT aac002 
                    FROM ac01 
                  WHERE aac031 = '1' 
                  GROUP BY aac002 
                  HAVING COUNT(aac002) > 1) 
)

select a.* from f a where not exists(select 1 from f b where 身份证号=a.身份证号 and name <>a.name)

[pt1314917]

--> 测试数据: [AC01]
if object_id('[AC01]') is not null drop table [AC01]
create table [AC01] (a int,身份证号 varchar(18),name varchar(6))
insert into [AC01]
select 66190,'230105197804170753','吴春波' union all
select 66191,'23010519780420006X','苗慧' union all
select 66192,'23010519780420006X','苗慧' union all
select 66193,'230105197804211914','陈辉' union all
select 66194,'230105197804211914','赵玉玺' union all
select 66195,'230105197804233718','杨保河' union all
select 66196,'230105197804233718','杨保河'


select a.* from [AC01] a ,[AC01] b where a.身份证号=b.身份证号 
and a.name <>b.name

[--小F--]

---6楼写错  修改下

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-21 09:13:23
-- Version:
--      Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
--	May  3 2005 23:18:38 
--	Copyright (c) 1988-2003 Microsoft Corporation
--	Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[身份证号] varchar(18),[姓名] varchar(6))
insert [tb]
select 66190,'230105197804170753','吴春波' union all
select 66191,'23010519780420006X','苗慧' union all
select 66192,'23010519780420006X','苗慧' union all
select 66193,'230105197804211914','陈辉' union all
select 66194,'230105197804211914','赵玉玺' union all
select 66195,'230105197804233718','杨保河' union all
select 66196,'230105197804233718','杨保河'
--------------开始查询--------------------------
select a.* from tb a where  exists(select 1 from tb b where 身份证号=a.身份证号 and [姓名] <>a.[姓名])
----------------结果----------------------------
/*
id          身份证号               姓名     
----------- ------------------ ------ 
66193       230105197804211914 陈辉
66194       230105197804211914 赵玉玺

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

[--小F--]

6楼应该用 exists

先把你的查询用cte查询 然后调用

如果是2000的话 放入临时表 或者嵌套

[YnSky]

select s.身份证号码,s.姓名 from tb as s,tb as a where s.身份证号=a.身份证号 
and s.name <>a.name

[misterliwei]

select aac002 身份证号, aac003 姓名
  from ac01
where aac002 in (SELECT aac002
                    FROM ac01
                  WHERE aac031 = '1'
                  GROUP BY aac002
                  HAVING COUNT(aac002) > 1) AND COUNT(DISTINCT aac003)>1 

[icelovey]

学习下EXISTS
declare @tb table([aac001] int,[aac002] varchar(18),[aac003] varchar(6))
insert @tb
select 66190,'230105197804170753','吴春波' union all
select 66191,'23010519780420006X','苗慧' union all
select 66192,'23010519780420006X','苗慧' union all
select 66193,'230105197804211914','陈辉' union all
select 66194,'230105197804211914','赵玉玺' union all
select 66195,'230105197804233718','杨保河' union all
select 66196,'230105197804233718','杨保河'

select * 
from @tb t
where exists(select 1 from @tb where aac002=t.aac002 and aac003<>t.aac003)

/*
aac001      aac002             aac003
----------- ------------------ ------
66193       230105197804211914 陈辉
66194       230105197804211914 赵玉玺

(2 row(s) affected)

[misterliwei]

select aac002 身份证号, aac003 姓名
  from ac01
where aac002 in (SELECT aac002
                    FROM ac01
                  WHERE aac031 = '1'
                  GROUP BY aac002
                  HAVING COUNT(aac002) > 1 AND COUNT(DISTINCT aac003)>1)