从数据中抽出连续三个月的值大于60的列子

Create Table vvv(FId Int,FDate varchar(20),FNum Int)
Insert Into vvv(FId,FDate,FNum)values(1,'2008-01',60)
Insert Into vvv(FId,FDate,FNum)values(1,'2008-02',70)
Insert Into vvv(FId,FDate,FNum)values(1,'2008-03',80)
Insert Into vvv(FId,FDate,FNum)values(1,'2008-04',70)
Insert Into vvv(FId,FDate,FNum)values(1,'2008-08',80)
Insert Into vvv(FId,FDate,FNum)values(2,'2008-01',50)
Insert Into vvv(FId,FDate,FNum)values(2,'2008-02',60)
Insert Into vvv(FId,FDate,FNum)values(2,'2008-03',70)

Insert Into vvv(FId,FDate,FNum)values(3,'2008-02',60)
Insert Into vvv(FId,FDate,FNum)values(3,'2008-03',50)
Insert Into vvv(FId,FDate,FNum)values(3,'2008-01',80)
Insert Into vvv(FId,FDate,FNum)values(3,'2008-02',60)
Insert Into vvv(FId,FDate,FNum)values(3,'2008-03',70)

方法一：

SELECT DISTINCT

    A.FId

FROM vvv A,

    vvv B,

    vvv C

WHERE A.FNum >= 60

    AND B.FNum >= 60

    AND C.FNum >= 60

    AND A.Fid = B.Fid

    AND DATEDIFF(Month, A.FDate + '-01', B.FDate + '-01') = 1

    AND B.Fid = C.Fid

    AND DATEDIFF(Month, B.FDate + '-01', C.FDate + '-01') = 1
方法二：

SELECT

    A.FId

FROM vvv A

WHERE A.FNum >= 60

    AND EXISTS(

           SELECT * FROM vvv B

           WHERE B.FNum >= 60

              AND A.Fid = B.Fid

              AND DATEDIFF(Month, A.FDate + '-01', B.FDate + '-01') = 1)

GROUP BY A.FId

HAVING COUNT(DISTINCT FDate) >= 2

方法三：

alter FUNCTION dbo.chk_num(
 @fId int,
 @mon varchar(20),
 @num int,
 @n int
)
RETURNS int
AS
BEGIN
  DECLARE @rq datetime, @erq datetime, @i int, @r int
  SET @rq = dateadd(mm, -@n, cast(@mon + '-01' AS datetime))
  SET @erq = dateadd(mm, @n, cast(@mon + '-01' AS datetime))
  SET @i = 0
  SET @r = 0
  IF NOT EXISTS(SELECT TOP 1 1 FROM vvv WHERE    fid = @fId AND FDate = @mon AND fnum >= @num )
    GOTO return_lab
  WHILE @rq < = @erq
  BEGIN
   IF EXISTS(SELECT TOP 1 1 FROM vvv WHERE  fid = @fId AND FDate = LEFT(CONVERT(varchar(10), @rq, 21), 7) and FNum >= @num)
   begin
     SET @i = @i + 1
     IF @i >= @n SET @r = 1
   end
   ELSE
   begin
    SET @i = 0
  end
   SET @rq = dateadd(mm, 1, @rq)
  END
  return_lab:
  RETURN @r
END
go
SELECT * FROM vvv WHERE dbo.chk_num(fid,Fdate,60,3) = 1