有一个表 id是从1开始的自增 增量为1
id year month day status
1 2011 1 1 500
2 2011 1 2 750
3 2011 1 3 500
4 2011 1 4 1000
4 2011 2 1 1000
......如何实现status连续为500的最大天数查询???
id year month day status
1 2011 1 1 500
2 2011 1 2 750
3 2011 1 3 500
4 2011 1 4 1000
4 2011 2 1 1000
......如何实现status连续为500的最大天数查询???
--这样?
go
create table [tb] (id int,year int,month int,day int,status int)
insert into [tb]
select 1,2011,1,1,500 union all
select 2,2011,1,2,750 union all
select 3,2011,1,3,500 union all
select 4,2011,1,4,1000 union all
select 5,2011,2,1,1000select * from [tb]
WITH tt
AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY STATUS ORDER BY GETDATE()) AS num
FROM dbo.TB
)--SELECT * FROM tt
SELECT MAX(id) AS id_ma,MIN(id) AS id_mi,COUNT(1) AS 连续天数 FROM TT WHERE STATUS =500 GROUP BY (num - id) HAVING COUNT(*) > 1
status=500的连续记录个数!!!!!
select count(*)
from(
select [status] cur,
(select min([status])
from tb b
where b.id>a.id)nxt,
[status]
from tb a
)as d
where cur-nxt=0
--2005可以参考5楼
孤岛问题:
create table t1
(
id int,
nian int,
yue int,
ri int,
sta int
)
insert into t1
select 1, 2011, 1, 1, 500 union all
select 2, 2011, 1, 2, 750 union all
select 3, 2011, 1, 3, 500 union all
select 4, 2011, 1, 4, 1000 union all
select 5, 2011, 2, 1, 1000
select * from t1;with aaa as
(select a.id as minid,b.id as maxid from t1 as a inner join t1 as b on a.sta=b.sta and a.id=b.id-1 and a.sta=1000)
select maxid-minid+1 from aaa