select Table1.* from Table1 right join ( select 姓名 From Table1 Group by 姓名 having Count(姓名) > 1 ) T on Table1.姓名= T.姓名
create table #TT ( 编号 nchar(10), 姓名 nvarchar(50) ) insert into #TT select '001','张三' insert into #TT select '002','李四' insert into #TT select '003','王五' insert into #TT select '004','张三' insert into #TT select '005','王五'select 编号,姓名 from ( select *, (select count(*) from #TT where 姓名=t.姓名) rank from #TT t ) tt where rank>1编号 姓名 ---------- -------------------------------------------------- 001 张三 003 王五 004 张三 005 王五
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE NAME=T.NAME AND ID<>T.ID)
--> 测试数据:@tb declare @tb table([编号] varchar(3),[姓名] varchar(4)) insert @tb select '001','张三' union all select '002','李四' union all select '003','王五' union all select '004','张三' union all select '005','王五'select * from @tb a right join (select [姓名] from @tb group by [姓名] having count([姓名])>1) t on a.[姓名]=t.[姓名] /* 编号 姓名 姓名 ---- ---- ---- 003 王五 王五 005 王五 王五 001 张三 张三 004 张三 张三(4 行受影响)*/
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-01-30 10:28:12 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#TB if object_id('tempdb.dbo.#TB') is not null drop table #TB go create table #TB([ID] varchar(3),[NAME] varchar(4)) insert #TB select '001','张三' union all select '002','李四' union all select '003','王五' union all select '004','张三' union all select '005','王五' --------------开始查询--------------------------SELECT * FROM #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE NAME=T.NAME AND ID<>T.ID) ----------------结果---------------------------- /* (所影响的行数为 5 行)ID NAME ---- ---- 001 张三 003 王五 004 张三 005 王五(所影响的行数为 4 行) */
SELECT 编号,姓名 FROM TABLE1 WHERE 姓名 IN ( SELECT 姓名 FROM TABLE1 GROUP BY COL2 HAVING COUNT(Col2) > 1 )
if object_id('dbo.tb') is not null drop table tb go create table tb ([ID] varchar(3),[NAME] varchar(4)) insert tb select '001','张三' union all select '002','李四' union all select '003','王五' union all select '004','张三' union all select '005','王五' select * from tb select * from tb a where exists(select * from tb b where b.id<>a.id and b.name=a.name)
select * from 表数据 a where exists(select 1 from 表数据 where 姓名=a.姓名 and 编号<>a.编号)
---------------------------------------------------------------- -- Author :Bigflower -- Date :2010-01-30 15:06:17.437 -- Version: --Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) -- Oct 14 2005 00:33:37 -- Copyright (c) 1988-2005 Microsoft Corporation -- Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3) ---------------------------------------------------------------- --> 测试数据:#TB IF OBJECT_ID('tempdb.dbo.#TB') IS NOT NULL DROP TABLE #TB go CREATE TABLE #TB(Sysno INT PRIMARY KEY,Name NVARCHAR(10)) INSERT #TB SELECT 001,'张三' UNION ALL SELECT 002,'李四' UNION ALL SELECT 003,'王五' UNION ALL SELECT 004 ,'张三' UNION ALL SELECT 005, '王五' --------------开始查询--------------------------SELECT B.Sysno,B.[Name] FROM (SELECT [Name] FROM #TB GROUP BY [Name] HAVING COUNT(*)>1) A LEFT JOIN #TB B ON A.[Name]=B.[Name]----------------结果---------------------------- /* Sysno Name ----------- ---------- 3 王五 5 王五 1 张三 4 张三(4 行受影响) */
select * from aaa a where exists(select 1 from aaa where id<>a.id and [name]=a.[name])
create table aaa( id int, [name] varchar(20) ) insert into aaa values(001,'张三') insert into aaa values(002,'李四') insert into aaa values(003,'王五') insert into aaa values(004,'张三') insert into aaa values(005,'王五')
declare @tb table([编号] varchar(3),[姓名] varchar(4)) insert @tb select '001','张三' union all select '002','李四' union all select '003','王五' union all select '004','张三' union all select '005','王五' select * from @tb where 姓名 in ( select 姓名 from @tb group by 姓名 having count(姓名) >1 )
right join
(
select 姓名 From Table1 Group by 姓名 having Count(姓名) > 1
) T
on Table1.姓名= T.姓名
(
编号 nchar(10),
姓名 nvarchar(50)
)
insert into #TT select '001','张三'
insert into #TT select '002','李四'
insert into #TT select '003','王五'
insert into #TT select '004','张三'
insert into #TT select '005','王五'select 编号,姓名 from
(
select *,
(select count(*) from #TT where 姓名=t.姓名) rank
from #TT t
) tt
where rank>1编号 姓名
---------- --------------------------------------------------
001 张三
003 王五
004 张三
005 王五
declare @tb table([编号] varchar(3),[姓名] varchar(4))
insert @tb
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','张三' union all
select '005','王五'select * from @tb a
right join
(select [姓名] from @tb group by [姓名] having count([姓名])>1) t
on a.[姓名]=t.[姓名]
/*
编号 姓名 姓名
---- ---- ----
003 王五 王五
005 王五 王五
001 张三 张三
004 张三 张三(4 行受影响)*/
-- Author :SQL77(只为思齐老)
-- Date :2010-01-30 10:28:12
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([ID] varchar(3),[NAME] varchar(4))
insert #TB
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','张三' union all
select '005','王五'
--------------开始查询--------------------------SELECT * FROM #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE NAME=T.NAME AND ID<>T.ID)
----------------结果----------------------------
/* (所影响的行数为 5 行)ID NAME
---- ----
001 张三
003 王五
004 张三
005 王五(所影响的行数为 4 行)
*/
FROM TABLE1
WHERE 姓名 IN
(
SELECT 姓名
FROM TABLE1
GROUP BY COL2
HAVING COUNT(Col2) > 1
)
go
create table tb ([ID] varchar(3),[NAME] varchar(4))
insert tb
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','张三' union all
select '005','王五'
select * from tb
select * from tb a where exists(select * from tb b where b.id<>a.id and b.name=a.name)
from 表数据 a
where exists(select 1 from 表数据 where 姓名=a.姓名 and 编号<>a.编号)
----------------------------------------------------------------
-- Author :Bigflower
-- Date :2010-01-30 15:06:17.437
-- Version:
--Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
----------------------------------------------------------------
--> 测试数据:#TB
IF OBJECT_ID('tempdb.dbo.#TB') IS NOT NULL DROP TABLE #TB
go
CREATE TABLE #TB(Sysno INT PRIMARY KEY,Name NVARCHAR(10))
INSERT #TB
SELECT 001,'张三'
UNION ALL
SELECT 002,'李四'
UNION ALL
SELECT 003,'王五'
UNION ALL
SELECT 004 ,'张三'
UNION ALL
SELECT 005, '王五' --------------开始查询--------------------------SELECT B.Sysno,B.[Name]
FROM (SELECT [Name]
FROM #TB GROUP BY [Name]
HAVING COUNT(*)>1) A
LEFT JOIN #TB B
ON A.[Name]=B.[Name]----------------结果----------------------------
/* Sysno Name
----------- ----------
3 王五
5 王五
1 张三
4 张三(4 行受影响)
*/
id int,
[name] varchar(20)
)
insert into aaa values(001,'张三')
insert into aaa values(002,'李四')
insert into aaa values(003,'王五')
insert into aaa values(004,'张三')
insert into aaa values(005,'王五')
insert @tb
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','张三' union all
select '005','王五'
select * from @tb where 姓名 in (
select 姓名 from @tb group by 姓名 having count(姓名) >1
)