---------------------------------------------------------------- -- 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 行受影响)*/
描述不够完整,不好处理.比如, 像helloworld这个数据,一天会出现多次么?
如果一天出现多次,就先预处理做到每天之取一次 ---------------------------------------------------------------- -- 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 行受影响)*/
-- 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 行受影响)*/