--如何求连续的个数create table tb (col1 int,col2 int); insert into tb select 1, 1 union select 2 ,9 union select 3, 9 union select 4, 2 union select 5, 9 union select 6, 9 union select 7, 9 union select 8, 9 union select 9, 9 union select 10, 9 union select 11, 1 union select 12, 9 union select 13, 9 union select 14, 9 union select 15, 9 union select 16, 9 with t as (select t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn from tb t1), t1 as ( select max(t.col1) as id, t.col2,COUNT(1) CNT from t group by t.col2,t.col1-rn) select * from t1 where cnt>=5 id col2 CNT ----------- ----------- ----------- 10 9 6 16 9 5(所影响的行数为 2 行)select max(a.col1) col1 ,COL2,count(1) as [count] from ( select L.*, ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM FROM tb L ) A group by A.COL2,GGM having count(1)>=5 order by min(A.COL1)col1 COL2 count ----------- ----------- ----------- 10 9 6 16 9 5(所影响的行数为 2 行)--2000的 if object_id('tb')is not null drop table tb go create table tb(id int,rq varchar(2),sktime varchar(5)) go insert tb select 1, '01', '08:10' union all select 1, '02', '08:11' union all select 1, '03', '08:10' union all select 1, '04', '08:10' union all select 1, '05', '08:20' union all select 1, '06', '08:20' union all select 1, '07', '08:20' union all select 1, '08', '08:30' select A.id,min(A.rq) rq ,A.sktime,count(1) as [count] from ( select L.id,L.rq,L.sktime, rowid=count(*)-L.rq FROM tb L left join tb M on L.sktime=M.sktime and L.rq>=M.rq group by L.id,L.rq,L.sktime ) A group by A.id,A.sktime,A.rowid having count(1)>=3 order by min(A.rq)/*id rq sktime count ----------- ---- ------ ----------- 1 05 08:20 3(所影响的行数为 1 行) */
select top 7 * from tb where jyrq<='2010-01-01 11:00:00' order by jyrq
if object_id('tbs') is not null drop table tbs select * into tbs from( select '001' zh, 490001 fse,'2010-01-01 17:00:01' jyrq union select '001',500002,'2010-01-01 17:00:02' union select '001',500002,'2010-01-02 17:00:03' union select '001',500002,'2010-01-02 17:00:04' union select '001',500002,'2010-01-03 17:00:05' union select '001',500002,'2010-01-03 17:00:06' union select '001',500002,'2010-01-04 11:00:07' union select '001',500002,'2010-01-05 17:00:08' union select '001',500002,'2010-01-06 17:00:09' union select '001',500002,'2010-01-06 17:00:10' union select '001',500002,'2010-01-07 17:00:11' union select '001',500002,'2010-01-08 17:00:12' union select '001',500002,'2010-01-08 18:00:13' )a select * from tbs select * from tbs where fse>500000 and stuff(jyrq,11,100,'') in (select top 7 max(stuff(jyrq,11,100,'')) jyrq from tbs group by substring(jyrq,9,2))
insert into tb
select 1, 1
union select 2 ,9
union select 3, 9
union select 4, 2
union select 5, 9
union select 6, 9
union select 7, 9
union select 8, 9
union select 9, 9
union select 10, 9
union select 11, 1
union select 12, 9
union select 13, 9
union select 14, 9
union select 15, 9
union select 16, 9
with t as
(select t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn from tb t1),
t1 as (
select max(t.col1) as id, t.col2,COUNT(1) CNT
from t
group by t.col2,t.col1-rn)
select * from t1 where cnt>=5
id col2 CNT
----------- ----------- -----------
10 9 6
16 9 5(所影响的行数为 2 行)select max(a.col1) col1 ,COL2,count(1) as [count]
from
(
select L.*,
ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM
FROM tb L
) A
group by A.COL2,GGM
having count(1)>=5
order by min(A.COL1)col1 COL2 count
----------- ----------- -----------
10 9 6
16 9 5(所影响的行数为 2 行)--2000的
if object_id('tb')is not null drop table tb
go
create table tb(id int,rq varchar(2),sktime varchar(5))
go
insert tb
select 1, '01', '08:10' union all
select 1, '02', '08:11' union all
select 1, '03', '08:10' union all
select 1, '04', '08:10' union all
select 1, '05', '08:20' union all
select 1, '06', '08:20' union all
select 1, '07', '08:20' union all
select 1, '08', '08:30'
select A.id,min(A.rq) rq ,A.sktime,count(1) as [count]
from
(
select L.id,L.rq,L.sktime,
rowid=count(*)-L.rq
FROM tb L left join tb M on L.sktime=M.sktime and L.rq>=M.rq
group by L.id,L.rq,L.sktime
) A
group by A.id,A.sktime,A.rowid
having count(1)>=3
order by min(A.rq)/*id rq sktime count
----------- ---- ------ -----------
1 05 08:20 3(所影响的行数为 1 行)
*/
=========================================
001 490001 2010-01-01 11:00:01
001 500002 2010-01-01 11:00:02
001 500003 2010-01-01 11:00:03
001 500004 2010-01-01 11:00:04
001 500005 2010-01-01 11:00:05
001 500006 2010-01-01 11:00:06
001 500007 2010-01-01 11:00:07
001 500008 2010-01-01 08:00:08
001 500009 2010-01-01 11:00:09
========
如果是需要11点时段的数据,是不是结果只要排除第1,8条数据啊???
select top 7 * from tb where jyrq<='2010-01-01 11:00:00' order by jyrq
if object_id('tbs') is not null
drop table tbs
select * into tbs from(
select '001' zh, 490001 fse,'2010-01-01 17:00:01' jyrq
union
select '001',500002,'2010-01-01 17:00:02'
union
select '001',500002,'2010-01-02 17:00:03'
union
select '001',500002,'2010-01-02 17:00:04'
union
select '001',500002,'2010-01-03 17:00:05'
union
select '001',500002,'2010-01-03 17:00:06'
union
select '001',500002,'2010-01-04 11:00:07'
union
select '001',500002,'2010-01-05 17:00:08'
union
select '001',500002,'2010-01-06 17:00:09'
union
select '001',500002,'2010-01-06 17:00:10'
union
select '001',500002,'2010-01-07 17:00:11'
union
select '001',500002,'2010-01-08 17:00:12'
union
select '001',500002,'2010-01-08 18:00:13'
)a
select * from tbs select * from tbs where fse>500000 and stuff(jyrq,11,100,'') in
(select top 7 max(stuff(jyrq,11,100,'')) jyrq from tbs group by substring(jyrq,9,2))