大家好 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呢?
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呢?
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 行受影响)
from tb s join tb t on s.身份证号=t.身份证号
where s.name <>t.name
select a.* from [AC01] a ,[AC01] b where a.身份证号=b.身份证号
and s.name <>t.name
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)
(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)
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
-- 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 行)
*/
select s.身份证号码,s.姓名 from tb as s,tb as a where s.身份证号=a.身份证号
and s.name <>a.name
from ac01
where aac002 in (SELECT aac002
FROM ac01
WHERE aac031 = '1'
GROUP BY aac002
HAVING COUNT(aac002) > 1) AND COUNT(DISTINCT aac003)>1
学习下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)
from ac01
where aac002 in (SELECT aac002
FROM ac01
WHERE aac031 = '1'
GROUP BY aac002
HAVING COUNT(aac002) > 1 AND COUNT(DISTINCT aac003)>1)