select count(1) from tab where exist (select * from tab a where tab.name=a.naem)
select * from tb t where exists(select 1 from tb where 编号<>t.编号 and 姓名=t.姓名)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-11-07 16:37:01 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer 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]([编号] int,[姓名] varchar(4)) insert [tb] select 1,'张三' union all select 2,'李四' union all select 3,'张三' --------------开始查询-------------------------- select * from tb t where exists(select 1 from tb where 编号<>t.编号 and 姓名=t.姓名) ----------------结果---------------------------- /* 编号 姓名 ----------- ---- 1 张三 3 张三(2 行受影响) */
select distinct 姓名 from 表 where (select count(*) from 表 as a where 姓名=表.姓名)>1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-07 16:37:01
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer 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]([编号] int,[姓名] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'李四' union all
select 3,'张三'
--------------开始查询--------------------------
select * from tb t where exists(select 1 from tb where 编号<>t.编号 and 姓名=t.姓名)
----------------结果----------------------------
/* 编号 姓名
----------- ----
1 张三
3 张三(2 行受影响)
*/
select distinct 姓名 from 表 where (select count(*) from 表 as a where 姓名=表.姓名)>1