----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-07 08:48:14
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 123,222,23 union all
select 223,221,23 union all
select 223,256,26 union all
select 123,22,27 union all
select 123,126,29 union all
select 223,225,558 union all
select 223,256,25 union all
select 123,223,30
--------------开始查询--------------------------
select
   distinct a.*
from
   tb a,tb b
where
   a.A in(select a from tb group by  a having COUNT(1)>=3)
and
   exists(select 1 from tb where a=b.A and a.C<=b.C-3)
and
   a.A=b.A
----------------结果----------------------------
/* A           B           C
----------- ----------- -----------
123         22          27
123         222         23
223         221         23
223         256         25
223         256         26

(5 行受影响)
*/

------解决方案--------------------
上次的回答:

select a.* from tb a inner join(
select *  
from tb a  
where exists(select 1 from tb where a=a.a and c<>a.c and c-a.c between 1 and 3)
and exists(select 1 from tb where a=a.a and c<>a.c and a.c-c between 1 and 3)
)b on a.a=b.a and abs(a.c-b.c)<=3

不满足需求吗?



------解决方案--------------------
SQL code
create table tb(A int,B int,C int)
insert into tb select 123,222,23
insert into tb select 223,221,23
insert into tb select 223,256,26
insert into tb select 123,22,27
insert into tb select 123,126,29
insert into tb select 223,225,558
insert into tb select 223,256,25
insert into tb select 123,223,30
go
select a.* from tb a inner join(
select a,c 
from tb t
where exists(select 1 from tb where a=t.a and c<>t.c and c-t.c between 0 and 3 group by a having count(*)=2)
)b on a.a=b.a and a.c-b.c between 0 and 3
/*
A           B           C
----------- ----------- -----------
123         22          27
123         126         29
123         223         30
223         221         23
223         256         26
223         256         25

(6 行受影响)

*/
go
drop table tb