求一SQL语句

[骑牛上铂金]

表
aa bb cc
01 2009-1-1 400
01 2009-1-4 500
01 2009-1-29 900
02 2008-12-5 4000
02 2008-12-29 300
02 2009-1-1 5000
02 2009-2-1 450
...

结果（要求显示出bb值间隔不超过3天的所有记录）
如下：
aa bb cc
01 2009-1-1 400
01 2009-1-4 500
02 2008-12-29 300
02 2009-1-1 5000


请帮忙解决,不甚感激！

[Stephen_Kang]

其实不用那么麻烦的。。。。、

只需。。获取bb列。。用函数。截取他们的 “日”（月/日/年） 用截取的两个“日”相减 where 条件就是。他们的值小于等于3

[htl258_Tony]

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([aa] varchar(10),[bb] datetime,[cc] int)
insert [tb] select '01','2009-1-1',400
union all select '01','2009-1-3',500
union all select '01','2009-1-4',500
union all select '01','2009-1-29',900
union all select '02','2008-12-5',4000
union all select '02','2008-12-29',300
union all select '02','2009-1-1',5000
union all select '02','2009-2-1',450

select *
from tb t
where exists
  (select 1 from tb where t.aa=aa
    and t.bb<>bb and abs(datediff(dd,t.bb,bb))<=3)

/*
aa         bb                      cc
---------- ----------------------- -----------
01         2009-01-01 00:00:00.000 400
01         2009-01-03 00:00:00.000 500
01         2009-01-04 00:00:00.000 500
02         2008-12-29 00:00:00.000 300
02         2009-01-01 00:00:00.000 5000

(5 行受影响)
*/

也可以用exists。

[firefly_2008]

LS 正解

列队顶

[ws_hgo]

[Quote=引用楼主 n29882942 的帖子:]
表
aa bb cc
01 2009-1-1 400
01 2009-1-4 500
01 2009-1-29 900
02 2008-12-5 4000
02 2008-12-29 300
02 2009-1-1 5000
02 2009-2-1 450
结果（要求显示出bb值间隔不超过3天的所有记录）
如下：
aa bb cc
01 2009-1-1 400
01 2009-1-4 500
02 2008-12-29 300
02 2009-1-1 5000
[/Quote]

select * from tb t where exists (select * from tb where aa=t.aa and datediff(day,aa.tt.aa)<=3)

[ws_hgo]

加个abs函数-->取绝对值的

select * from tb t where exists (select * from tb where aa=t.aa and abs(datediff(day,aa.tt.aa))<=3)

[骑牛上铂金]

谢谢！

[htl258_Tony]

try:

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([aa] varchar(10),[bb] datetime,[cc] int)
insert [tb] select '01','2009-1-1',400
union all select '01','2009-1-4',500
union all select '01','2009-1-29',900
union all select '02','2008-12-5',4000
union all select '02','2008-12-29',300
union all select '02','2009-1-1',5000
union all select '02','2009-2-1',450

select a.*
from tb a
  join tb b
    on a.aa=b.aa
    and a.bb<>b.bb
where abs(datediff(dd,a.bb,b.bb))<=3
/*
aa         bb                      cc
---------- ----------------------- -----------
01         2009-01-01 00:00:00.000 400
01         2009-01-04 00:00:00.000 500
02         2008-12-29 00:00:00.000 300
02         2009-01-01 00:00:00.000 5000

(4 行受影响)
*/

[htl258_Tony]

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([aa] varchar(10),[bb] datetime,[cc] int)
insert [tb] select '01','2009-1-1',400
union all select '01','2009-1-3',500
union all select '01','2009-1-4',500
union all select '01','2009-1-29',900
union all select '02','2008-12-5',4000
union all select '02','2008-12-29',300
union all select '02','2009-1-1',5000
union all select '02','2009-2-1',450

select distinct a.*
from tb a
  join tb b
    on a.aa=b.aa
    and a.bb<>b.bb
where abs(datediff(dd,a.bb,b.bb))<=3
/*
aa         bb                      cc
---------- ----------------------- -----------
01         2009-01-01 00:00:00.000 400
01         2009-01-03 00:00:00.000 500
01         2009-01-04 00:00:00.000 500
02         2008-12-29 00:00:00.000 300
02         2009-01-01 00:00:00.000 5000

(5 行受影响)
*/

再加入一个日期调期。小改一下。