sql 2005 筛选 2项相同，一项不同的记录

[疯飙的蜗牛]

我的 select_bb 表有 name ， server ，bb 列，数据都是varchar（50）类型

筛选条件 筛选出 server 和bb 相同，而 name 不相同的 数据

表数据举例：
name server bb
a server1 bb1
a server1 bb1
b server2 bb1
b server2 bb1
a server1 bb2
c server1 bb1
d server1 bb2

筛选出结果
name server bb
a server1 bb1
a server1 bb2
c server1 bb1
d server1 bb2

求大神 赐 sql 语句！！！

[疯飙的蜗牛]

在线等 大神！！！！！！！！！！！！！！！！！！！！

[哥眼神纯洁不]

with tb(name,server,bb)as(
select 'a','server1','bb1' union all
select 'a','server1','bb1' union all
select 'b','server2','bb1' union all
select 'b','server2','bb1' union all
select 'a','server1','bb2' union all
select 'c','server1','bb1' union all
select 'd','server1','bb2' 
)
select distinct * from tb a 
where exists (select 1 from (select distinct * from tb)b group by server,bb
having COUNT(1)>1 and a.server=server and a.bb=bb)

方法很多，我写一个...

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-14 17:29:29
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[select_bb]
if object_id('[select_bb]') is not null drop table [select_bb]
go 
create table [select_bb]([name] varchar(1),[server] varchar(7),[bb] varchar(3))
insert [select_bb]
select 'a','server1','bb1' union all
select 'a','server1','bb1' union all
select 'b','server2','bb1' union all
select 'b','server2','bb1' union all
select 'a','server1','bb2' union all
select 'c','server1','bb1' union all
select 'd','server1','bb2'
--------------开始查询--------------------------
SELECT DISTINCT a.*
FROM [select_bb] a INNER JOIN [select_bb] b ON a.[server]=b.[server] AND a.[bb]=b.[bb] AND a.[name]<>b.[name]

----------------结果----------------------------
/* 
name server  bb
---- ------- ----
a    server1 bb1
a    server1 bb2
c    server1 bb1
d    server1 bb2
*/

[aa659401538]

DISTINCT 用这个就可以去重

[奔四在望]

引用 3 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-14 17:29:29
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[select_bb]
if object_id('[select_bb]') is not null drop table [select_bb]
go 
create table [select_bb]([name] varchar(1),[server] varchar(7),[bb] varchar(3))
insert [select_bb]
select 'a','server1','bb1' union all
select 'a','server1','bb1' union all
select 'b','server2','bb1' union all
select 'b','server2','bb1' union all
select 'a','server1','bb2' union all
select 'c','server1','bb1' union all
select 'd','server1','bb2'
--------------开始查询--------------------------
SELECT DISTINCT a.*
FROM [select_bb] a INNER JOIN [select_bb] b ON a.[server]=b.[server] AND a.[bb]=b.[bb] AND a.[name]<>b.[name]

----------------结果----------------------------
/* 
name server  bb
---- ------- ----
a    server1 bb1
a    server1 bb2
c    server1 bb1
d    server1 bb2
*/

正解