查询一定时间内完全重复的数据
本帖最后由 CrazyPredators_ 于 2013-01-19 22:53:59 编辑 date text
2013-1-1 helloworld
2013-1-1 test1
2013-1-2 helloworld
2013-1-3 test2
2013-1-3 helloworld
2013-1-4 helloworld
2013-1-5 test3
现在需要写一个方法 输入一个时间段查找重复的记录
例如：
1-1 ~ 1-4
结果:
helloworld
1-1 ~ 1-5
结果：
没有记录
[解决办法]
----------------------------
-- Author :磊仔
-- Date :2013-01-20 01:04:24
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
--Sep 16 2010 20:09:22
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([date] date,[text] varchar(10))
insert #TA
select '2013-1-1','helloworld' union all
select '2013-1-1','test1' union all
select '2013-1-2','helloworld' union all
select '2013-1-3','test2' union all
select '2013-1-3','helloworld' union all
select '2013-1-4','helloworld' union all
select '2013-1-5','test3'
--------------开始查询--------------------------
declare @bgn date,@end date
select @bgn = '2013-1-1', @end = '2013-1-4'
select [text]
from #TA
group by [text]
having COUNT([text]) > DATEDIFF(DD,@bgn,@end)
----------------结果----------------------------
/*
text
----------
helloworld
(1 行受影响)
*/
----------------------------
-- Author :磊仔
-- Date :2013-01-20 01:04:24
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
--Sep 16 2010 20:09:22
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([date] date,[text] varchar(10))
insert #TA
select '2013-1-1','helloworld' union all
select '2013-1-1','helloworld' union all
select '2013-1-1','test1' union all
select '2013-1-1','test1' union all
select '2013-1-1','test1' union all
select '2013-1-1','test1' union all
select '2013-1-1','test1' union all
select '2013-1-2','helloworld' union all
select '2013-1-3','test2' union all
select '2013-1-3','helloworld' union all
select '2013-1-4','helloworld' union all
select '2013-1-5','test3'
--------------开始查询--------------------------
declare @bgn date,@end date
select @bgn = '2013-1-1', @end = '2013-1-4'
;with cet as
(select distinct date,[text] from #TA)
select [text]
from cet
group by [text]
having COUNT([text]) > DATEDIFF(DD,@bgn,@end)
----------------结果----------------------------
/*
text
----------
helloworld
(1 行受影响)
*/
