如何查询?

[panxf316]

CREATE TABLE #Num
{
Name NVARCHAR ( 100 ),
Date DATETIME,
Value NVARCHAR ( 100 )
}
insert #num values('a','2003-06-08','F')
insert #num values('a','2003-07-08','T')
insert #num values('a','2003-07-29','T')
insert #num values('a','2003-08-19','T')
insert #num values('a','2003-09-11','T')
insert #num values('a','2003-10-21','T')
insert #num values('b','2003-07-29','T')
insert #num values('b','2003-08-19','F')
insert #num values('b','2003-09-11','T')
insert #num values('b','2003-10-21','T')
insert #num values('c','2003-07-08','T')
insert #num values('c','2003-09-11','T')
insert #num values('c','2003-10-21','T')
如果要求查询从7月份开始到现在这段时间之内,每个月都存在至少一个Value为'T'的name的话,那查询的结果应该是:
Name
a
如果要求查询从9月份开始到现在这段时间之内,每个月都存在至少一个Value为'T'的name的话,那查询的结果应该是:
Name
a
b
c
该如何查询呢?谢谢!

[pengdali]

CREATE TABLE #Num
(
Name NVARCHAR ( 100 ),
Date DATETIME,
Value NVARCHAR ( 100 )
)
insert #num values('a','2003-06-08','F')
insert #num values('a','2003-07-08','T')
insert #num values('a','2003-07-29','T')
insert #num values('a','2003-08-19','T')
insert #num values('a','2003-09-11','T')
insert #num values('a','2003-10-21','T')
insert #num values('b','2003-07-29','T')
insert #num values('b','2003-08-19','F')
insert #num values('b','2003-09-11','T')
insert #num values('b','2003-10-21','T')
insert #num values('c','2003-07-08','T')
insert #num values('c','2003-09-11','T')
insert #num values('c','2003-10-21','T')

select name from #num where Value='T' and date between '2003-07-01' and getdate() group by name having count(distinct datediff(month,[date],getdate()))>=datediff(month,'2003-07-01',getdate())+1

go
drop table #num

[pengdali]

CREATE TABLE #Num
(
Name NVARCHAR ( 100 ),
Date DATETIME,
Value NVARCHAR ( 100 )
)
insert #num values('a','2003-06-08','F')
insert #num values('a','2003-07-08','T')
insert #num values('a','2003-07-29','T')
insert #num values('a','2003-08-19','T')
insert #num values('a','2003-09-11','T')
insert #num values('a','2003-10-21','T')
insert #num values('b','2003-07-29','T')
insert #num values('b','2003-08-19','F')
insert #num values('b','2003-09-11','T')
insert #num values('b','2003-10-21','T')
insert #num values('c','2003-07-08','T')
insert #num values('c','2003-09-11','T')
insert #num values('c','2003-10-21','T')

declare @a datetime
set @a='2003-09-01'

select name from #num where Value='T' and date between @a and getdate() group by name having count(distinct datediff(month,[date],getdate()))>=datediff(month,@a,getdate())+1

go
drop table #num


------结果：


name
----------------------------------------------------------------------------------------------------
a
b
c

[txlicenhe]

CREATE TABLE #Num
(
Name NVARCHAR ( 100 ),
Date DATETIME,
Value NVARCHAR ( 100 )
)
insert #num values('a','2003-06-08','F')
insert #num values('a','2003-07-08','T')
insert #num values('a','2003-07-29','T')
insert #num values('a','2003-08-19','T')
insert #num values('a','2003-09-11','T')
insert #num values('a','2003-10-21','T')
insert #num values('b','2003-07-29','T')
insert #num values('b','2003-08-19','F')
insert #num values('b','2003-09-11','T')
insert #num values('b','2003-10-21','T')
insert #num values('c','2003-07-08','T')
insert #num values('c','2003-09-11','T')
insert #num values('c','2003-10-21','T')
go

create procedure test @a datetime
As
Select name from (
Select name,convert(char(6),date,112) as date,count(*) as cnt from #num
where value = 'T' and date > @a
group by name,convert(char(6),date,112)
) tmp
group by name
having count(*) > datediff(month,@a,getdate())
go

exec test '2003-07-01'



--drop table #num
-- drop procedure test

[txlicenhe]

exec test '2003-07-01'
name
----------------------------------------------------------------------------------------------------
a

（所影响的行数为 1 行）

exec test '2003-08-01'
name
----------------------------------------------------------------------------------------------------
a

（所影响的行数为 1 行）
exec test '2003-09-01'
name
----------------------------------------------------------------------------------------------------
a
b
c

（所影响的行数为 3 行）

[wzh1215]

呵呵，马可的方法真是妙！

[pengdali]

sp1:

select name from #num where Value='T' and date between @a and getdate() group by name having count(distinct datediff(month,[date],getdate()))>datediff(month,@a,getdate())

[panxf316]

首先我要说的一点是两个方法都很好，都能有效的解决我的问题，但是由于txlicenhe(马可) 用的是通过生成临时表进行再查询，效率上相信没有pengdali(大力 V3.0)快，所以我还是喜欢pengdali(大力 V3.0)的方法