非空、重复数据的统计查询 SQL2005

[多难成佛]

查询字段值的重复号码
表A，字段如下（数据ID是自增主键）：
数据ID 学生ID 电话号码 班级 班主任
1 035 8785465
2 035 8865453
3 186 8888888
4 186
5 033 7654832
6 042 8888888
7 033 8324231
8 012 8357623
9 039 9999999
10 177
11 027 9999999
12 027
---------------------------------------
在非空的电话号码中查询重复的电话号码（按号码计数而非按记录计数），得到下面这样的结果：
数据ID 学生ID 电话号码
3 186 8888888
6 042 8888888
9 039 9999999
11 027 9999999

[百年树人]

if object_id('[A]') is not null drop table [A]
go
create table [A]([数据ID] int,[学生ID] varchar(3),[电话号码] int,[班级] sql_variant,[班主任] sql_variant)
insert [A]
select 1,'035',8785465,null,null union all
select 2,'035',8865453,null,null union all
select 3,'186',8888888,null,null union all
select 4,'186',null,null,null union all
select 5,'033',7654832,null,null union all
select 6,'042',8888888,null,null union all
select 7,'033',8324231,null,null union all
select 8,'012',8357623,null,null union all
select 9,'039',9999999,null,null union all
select 10,'177',null,null,null union all
select 11,'027',9999999,null,null union all
select 12,'027',null,null,null
go

select A.数据ID,A.学生ID,A.电话号码 
from A
JOIN (SELECT 电话号码 FROM A GROUP BY 电话号码 HAVING COUNT(1)>1) B
ON A.电话号码=B.电话号码

/*
数据ID        学生ID 电话号码
----------- ---- -----------
3           186  8888888
6           042  8888888
9           039  9999999
11          027  9999999

(4 行受影响)
*/

[骑着蜗牛去爬山]

with tb(数据ID,学生ID,电话号码)
as(
select 1,'035','8785465' union all
select 2,'035','8865453'  union all      
select 3,'186','8888888'union all
select 4,'186',null   union all
select 5,'033','7654832'union all
select 6,'042','8888888'union all
select 7,'033','8324231'union all
select 8,'012','8357623'union all
select 9,'039','9999999'union all
select 10,'177',null   union all
select 11,'027','9999999'union all
select 12,'027',null
)select * from tb tb1 where (select count(1) from tb tb2 where isnull(tb1.电话号码,'')=tb2.电话号码)>1

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([数据ID] int,[学生ID] varchar(3),[电话号码] int,[班级] sql_variant,[班主任] sql_variant)
insert [TB]
select 1,'035',8785465,null,null union all
select 2,'035',8865453,null,null union all
select 3,'186',8888888,null,null union all
select 4,'186',null,null,null union all
select 5,'033',7654832,null,null union all
select 6,'042',8888888,null,null union all
select 7,'033',8324231,null,null union all
select 8,'012',8357623,null,null union all
select 9,'039',9999999,null,null union all
select 10,'177',null,null,null union all
select 11,'027',9999999,null,null union all
select 12,'027',null,null,null

select 
     [数据ID],
     [学生ID],
     [电话号码]
from [TB] t 
WHERE  EXISTS(
     SELECT 1 FROM Tb where t.[电话号码]>[电话号码] and t.[数据ID]>[数据ID] and t.[学生ID]>[学生ID]
     )

/*
数据ID        学生ID 电话号码
----------- ---- -----------
3           186  8888888
6           042  8888888
9           039  9999999
11          027  9999999

(4 行受影响)

*/

drop table [TB]