34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(NAME VARCHAR, DT DATETIME, VAL INT)
INSERT @t SELECT 'A', '2008-06-17', 10
UNION ALL SELECT 'A', '2008-06-16', 09
UNION ALL SELECT 'A', '2008-06-15', 08
UNION ALL SELECT 'A', '2008-06-14', 06
UNION ALL SELECT 'A', '2008-06-13', -1
UNION ALL SELECT 'A', '2008-06-12', 07
UNION ALL SELECT 'A', '2008-06-11', 13
UNION ALL SELECT 'B', '2008-06-17', 10
UNION ALL SELECT 'B', '2008-06-16', 09
UNION ALL SELECT 'B', '2008-06-15', 0
UNION ALL SELECT 'B', '2008-06-14', 02
UNION ALL SELECT 'B', '2008-06-13', -1
UNION ALL SELECT 'B', '2008-06-12', 07
UNION ALL SELECT 'B', '2008-06-11', 10
UNION ALL SELECT 'C', '2008-06-17', 0
UNION ALL SELECT 'C', '2008-06-16', 09
UNION ALL SELECT 'C', '2008-06-15', 0
UNION ALL SELECT 'C', '2008-06-14', 06
UNION ALL SELECT 'C', '2008-06-13', 225
UNION ALL SELECT 'C', '2008-06-12', 150
UNION ALL SELECT 'C', '2008-06-11', 100
--连续4天内有2次val都大于5的
DECLARE @d INT,@val INT,@v INT
SELECT @d=4,@val=5,@v=2
--鉴于中间满足条件的日期不是连续的,所以我多连了一次表,列出了符合条件的组合。 实际上,在上贴的回贴中得到的也是类似于子查询这一部分,要得到组合同样的多一次连表即可
SELECT x.Name Name,x.dt GroupStart,y.dt CurrentDateInGroup,y.val CurrentVal
FROM
(SELECT * FROM @t a WHERE (SELECT COUNT(*) FROM @t WHERE a.name=name AND dt BETWEEN a.dt AND DATEADD(dd,@d-1,a.dt) AND val>@val)>=@v) x
INNER JOIN @t y
ON x.name=y.name AND y.dt BETWEEN x.dt AND DATEADD(dd,@d-1,x.dt) AND y.val>@val
--至于第二个问题,你所谓的增加,是基于始起日期增加,还是递增(而不管增量是否相同)
DECLARE @t TABLE(NAME VARCHAR, DT DATETIME, VAL INT)
INSERT @t SELECT 'A', '2008-06-17', 10
UNION ALL SELECT 'A', '2008-06-16', 09
UNION ALL SELECT 'A', '2008-06-15', 08
UNION ALL SELECT 'A', '2008-06-14', 06
UNION ALL SELECT 'A', '2008-06-13', -1
UNION ALL SELECT 'A', '2008-06-12', 07
UNION ALL SELECT 'A', '2008-06-11', 13
UNION ALL SELECT 'B', '2008-06-17', 10
UNION ALL SELECT 'B', '2008-06-16', 09
UNION ALL SELECT 'B', '2008-06-15', 0
UNION ALL SELECT 'B', '2008-06-14', 02
UNION ALL SELECT 'B', '2008-06-13', -1
UNION ALL SELECT 'B', '2008-06-12', 07
UNION ALL SELECT 'B', '2008-06-11', 10
UNION ALL SELECT 'C', '2008-06-17', 0
UNION ALL SELECT 'C', '2008-06-16', 09
UNION ALL SELECT 'C', '2008-06-15', 0
UNION ALL SELECT 'C', '2008-06-14', 06
UNION ALL SELECT 'C', '2008-06-13', 225
UNION ALL SELECT 'C', '2008-06-12', 150
UNION ALL SELECT 'C', '2008-06-11', 100
SELECT * FROM @t ORDER BY Name,dt
--连续4天递增的
DECLARE @d INT
SELECT @d=4
SELECT * FROM @t a WHERE EXISTS(SELECT 1 FROM @t WHERE DATEDIFF(dd,a.dt,dt) BETWEEN 0 AND @d-1 AND name=a.Name
AND NOT EXISTS(SELECT 1 FROM @t b WHERE dt BETWEEN DATEADD(dd,1,a.dt) AND DATEADD(dd,@d-1,a.dt) AND name=a.Name
AND EXISTS(SELECT 1 FROM @t WHERE name=b.name AND DATEDIFF(dd,dt,b.dt)=1 AND b.val-val<=0)
)
GROUP BY Name HAVING COUNT(*)>=@d)
SELECT * FROM @t a WHERE EXISTS(SELECT 1 FROM @t b WHERE DATEDIFF(dd,a.dt,dt) BETWEEN 0 AND @d-1 AND val>@val AND name=a.Name)GROUP BY Name HAVING COUNT(*)>=@d)
DECLARE @t TABLE(NAME VARCHAR, DT DATETIME, VAL INT)
INSERT @t SELECT 'A', '2008-06-17', 10
UNION ALL SELECT 'A', '2008-06-16', 09
UNION ALL SELECT 'A', '2008-06-15', 08
UNION ALL SELECT 'A', '2008-06-14', 06
UNION ALL SELECT 'A', '2008-06-13', -1
UNION ALL SELECT 'A', '2008-06-12', 07
UNION ALL SELECT 'A', '2008-06-11', 13
UNION ALL SELECT 'B', '2008-06-17', 10
UNION ALL SELECT 'B', '2008-06-16', 09
UNION ALL SELECT 'B', '2008-06-15', 0
UNION ALL SELECT 'B', '2008-06-14', 02
UNION ALL SELECT 'B', '2008-06-13', -1
UNION ALL SELECT 'B', '2008-06-12', 07
UNION ALL SELECT 'B', '2008-06-11', 10
UNION ALL SELECT 'C', '2008-06-17', 0
UNION ALL SELECT 'C', '2008-06-16', 09
UNION ALL SELECT 'C', '2008-06-15', 0
UNION ALL SELECT 'C', '2008-06-14', 06
UNION ALL SELECT 'C', '2008-06-13', 225
UNION ALL SELECT 'C', '2008-06-12', 150
UNION ALL SELECT 'C', '2008-06-11', 100
--连续4天val都大于5的
DECLARE @d INT,@val INT
SELECT @d=4,@val=5
SELECT * FROM @t a WHERE EXISTS(SELECT 1 FROM @t b WHERE DATEDIFF(dd,a.dt,dt) BETWEEN 0 AND @d-1 AND val>@val AND name=a.Name
AND NOT EXISTS(SELECT 1 FROM @t WHERE dt BETWEEN a.dt AND DATEADD(dd,@d-1,a.dt) AND Name=a.Name AND val<=@val)
GROUP BY Name HAVING COUNT(*)>=@d)
/*
A 2008-06-14 00:00:00.000 6
C 2008-06-11 00:00:00.000 100
即,得到这两条记录为对应的Name里起始日期,那连以它们为初始,连续的@d天内,val都是大于@val的
如果原始记录还有一条 A 2008-06-18 20 这样的记录,那么, A的6-15也会被选出,即一共有5天是连续的。也就是说从6-14起始是满足的(到6-17),从6-15也是满足的(到6-18)
取到初始值,怎么得到你要求的结果,这已经不重要了。
或者说,你要求的是最近的那一条,我给出的是最远的那一条,本质一样,反着写一下就可以了。
*/
--连续3天每天递增1的
SELECT @d=3,@val=1
SELECT * FROM @t a WHERE EXISTS(SELECT 1 FROM @t WHERE DATEDIFF(dd,a.dt,dt) BETWEEN 0 AND @d-1 AND name=a.Name
AND NOT EXISTS(SELECT 1 FROM @t b WHERE dt BETWEEN DATEADD(dd,1,a.dt) AND DATEADD(dd,@d-1,a.dt) AND name=a.Name
AND EXISTS(SELECT 1 FROM @t WHERE name=b.name AND DATEDIFF(dd,dt,b.dt)=1 AND b.val-val!=@val)
)
GROUP BY Name HAVING MAX(val)-MIN(val)=(@d-1)*@val)
/*
A 2008-06-15 00:00:00.000 8
结果意义同第一个
*/
第三个就不写了,跟第二天比,改改过节滤条件里的算述运算就可以了,换汤不换药
乱写的,不保证正确。
select a.*
from 表 a inner join 表 b on a.name=b.name and a.dt=b.dt-1
inner join 表 c on a.name=c.name and b.dt=c.dt-1
inner join 表 d on a.name=d.name and c.dt=d.dt-1
where a.val=b.val+1
and b.val>=c.val+1
and c.val>=d.val+1
.select a.*
from 表 a inner join 表 b on a.name=b.name and a.dt=b.dt-1
inner join 表 c on a.name=c.name and b.dt=c.dt-1
inner join 表 d on a.name=d.name and c.dt=d.dt-1
where a.val>5 and b.val>5 and c.val>5 and d.val>5
.