同个表里面同一组记录不同其它字段值的问题.

[lingdove]

我有一个表,里面有A和B两个字段[都不是主键],合理的数据是A字段如果的值是1,那么B字段所有的值是一样的,现在想找出A字段的是一个值,但是B字段里面是两个值的记录要怎么找??

A B
1 100
1 100
1 100
2 101
2 101
2 103
3 104
3 104

像这样子,倒数第三条就是有问题的记录.
请教高手....

[ACMAIN_CHM]

select *
from 一个表 t
where exists (select 1 from 一个表 where a=t.a and b <> t.b)

[sgtzzc]

select *
from tb t
where (select count(distinct b) from tb where a=t.a)>1

[ACMAIN_CHM]

1> select * from lingdove
2> go
a          |b
-----------|-----------
          1|        100
          1|        100
          1|        100
          2|        101
          2|        101
          2|        103
          3|        104
          3|        104

(8 rows affected)
1>
2> select *
3> from lingdove  t
4> where exists (select 1 from lingdove where a=t.a and b <> t.b)
5> go
a          |b
-----------|-----------
          2|        101
          2|        101
          2|        103

(3 rows affected)
1>

[dawugui]

create table tb(A int,       B int)
insert into tb values(1 ,       100 )
insert into tb values(1 ,       100 )
insert into tb values(1 ,       100 )
insert into tb values(2 ,       101 )
insert into tb values(2 ,       101 )
insert into tb values(2 ,       103 )
insert into tb values(3 ,       104 )
insert into tb values(3 ,       104 )
go
select * from tb where a in
(
select a from
(
  select distinct a , b from tb 
) t
group by a having count(1) > 1
)

drop table tb

/*

A           B           
----------- ----------- 
2           101
2           101
2           103

（所影响的行数为 3 行）
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-12-21 00:06:19
-- 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]([A] int,[B] int)
insert [tb]
select 1,100 union all
select 1,100 union all
select 1,100 union all
select 2,101 union all
select 2,101 union all
select 2,103 union all
select 3,104 union all
select 3,104
--------------开始查询--------------------------
select
 *
from
 tb t
where
 exists (select 1 from tb where a=t.a and b <> t.b)
----------------结果----------------------------
/* A           B
----------- -----------
2           101
2           101
2           103

(3 行受影响)

*/

[-狙击手-]

create table tb(A int,       B int)
insert into tb values(1 ,       100 )
insert into tb values(1 ,       100 )
insert into tb values(1 ,       100 )
insert into tb values(2 ,       101 )
insert into tb values(2 ,       101 )
insert into tb values(2 ,       103 )
insert into tb values(3 ,       104 )
insert into tb values(3 ,       104 )
go

;with tt
as
(select rid = row_number() over (partition by a order by getdate()),*
 from tb)
select * from tt b
where  exists(select 1 from tt where a = b.a and b!=b.b and rid < b.rid)
 

drop table tb


rid                  A           B
-------------------- ----------- -----------
3                    2           103