SELECT value
FROM test t
WHERE EXISTS(
SELECT 1
FROM test t1
WHERE t.value=t1.value
AND t.date<>t1.DATE)
FROM test t
WHERE EXISTS(
SELECT 1
FROM test t1
WHERE t.value=t1.value
AND t.date<>t1.DATE)
select value from test group by value having count(*)>1
insert @t
select '123','2006-1-1' union all
select '333','2006-1-1' union all
select '555','2006-1-1' union all
select '777','2006-1-1' union all
select '666','2006-1-1' union all
select '123','2006-1-2' union all
select '125','2006-1-2' union all
select '333','2006-1-2' union all
select '888','2006-1-2' union all
select '123','2006-1-3' union all
select '126','2006-1-3' union all
select '333','2006-1-3' union all
select '888','2006-1-3'select distinct A.VALUE from @T A,@T B,
(select 1 as N
union all select 2
union all select 0
) C
where A.VALUE=B.VALUE
and B.[date]=dateadd(d,N,A.[date])
group by A.VALUE
having(count(*)>3)/*VALUE
-----
123
333*/
from test
where date>'2006-01-01' and date <'2006-01-04'
group by value
having count(*)>1
FROM test t
WHERE EXISTS(
SELECT 1
FROM
(select value,date from test
WHERE value=t.value
AND date=t.date
union all
select value,date from test
WHERE value=t.value
AND date=dateadd(dd,1,t.date)
union all
select value,date from test
WHERE value=t.value
AND date=dateadd(dd,2,t.date)
)
where date>=dateadd(day,-1,getdate())
and date<=dateadd(day,3,getdate())
group by value
having count(value)>1
SELECT value
FROM test t
WHERE EXISTS(
SELECT 1
FROM
(select ditinct value from test
WHERE value=t.value
AND date=t.date
union all
select distinct value from test
WHERE value=t.value
AND date=dateadd(dd,1,t.date)
union all
select distinct value from test
WHERE value=t.value
AND date=dateadd(dd,2,t.date)
) a group by a.value having count(*)=3)
(VALUE varchar(10),Date datetime)
insert @test
select '123','2006-1-1' union all
select '333','2006-1-1' union all
select '555','2006-1-1' union all
select '777','2006-1-1' union all
select '666','2006-1-1' union all
select '123','2006-1-2' union all
select '123','2006-1-2' union all
select '333','2006-1-2' union all
select '888','2006-1-2' union all
select '333','2006-1-3' union all
select '888','2006-1-3' union all
select '123','2006-1-4' union all
select '888','2006-1-4' union all
select '888','2006-1-4'select distinct a.value from @test a
inner join @test b on a.value=b.value and datediff(d,a.date,b.date)=1
inner join @test c on a.value=c.value and datediff(d,a.date,c.date)=2 and datediff(d,b.date,c.date)=1
value
----------
333
888(所影响的行数为 2 行)