求数据统计的sql语句

shabble 2010-10-20 05:07:33



if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([cardid] int,[status] int,[time] varchar(20))

insert [tb]

select 1010,0,'2009-10-25 01:00:00' union all

select 1010,0,'2009-10-25 01:01:00' union all

select 1010,0,'2009-10-25 01:02:00' union all

select 1011,1,'2009-10-25 01:03:00' union all

select 1011,1,'2009-10-25 01:04:00' union all

select 1011,1,'2009-10-25 01:05:00' union all

select 1011,1,'2009-10-25 01:06:00' union all

select 1012,0,'2009-10-25 01:07:00' union all

select 1012,0,'2009-10-25 01:08:00' union all

select 1012,0,'2009-10-25 01:09:00' union all

select 1013,0,'2009-10-25 01:10:00' union all

select 1013,1,'2009-10-25 01:11:00' union all

select 1013,1,'2009-10-25 01:12:00' union all

select 1013,1,'2009-10-25 01:13:00' union all

select 1013,0,'2009-10-25 01:14:00' union all

select 1013,1,'2009-10-25 01:15:00' union all

select 1013,1,'2009-10-25 01:16:00' union all

select 1013,0,'2009-10-25 01:17:00' union all

select 1013,1,'2009-10-25 01:18:00' union all

select 1013,1,'2009-10-25 01:19:00' union all

select 1013,1,'2009-10-25 01:20:00' union all

select 1013,1,'2009-10-25 01:21:00' union all

select 1013,1,'2009-10-25 01:22:00' union all

select 1013,1,'2009-10-25 01:23:00'

我希望统计[status]字段，将[status]连续为1的次数统计出来。目标输出

cardid status time count
----------- ----------- -------------------- ---------
1010 0 2009-10-25 01:00:00 0
1010 0 2009-10-25 01:01:00 0
1010 0 2009-10-25 01:02:00 0
1011 1 2009-10-25 01:03:00 1
1011 1 2009-10-25 01:04:00 2
1011 1 2009-10-25 01:05:00 3
1011 1 2009-10-25 01:06:00 4
1012 0 2009-10-25 01:07:00 0
1012 0 2009-10-25 01:08:00 0
1012 0 2009-10-25 01:09:00 0
1013 0 2009-10-25 01:10:00 0
1013 1 2009-10-25 01:11:00 1
1013 1 2009-10-25 01:12:00 2
1013 1 2009-10-25 01:13:00 3
1013 0 2009-10-25 01:14:00 0
1013 1 2009-10-25 01:15:00 1
1013 1 2009-10-25 01:16:00 2
1013 0 2009-10-25 01:17:00 0
1013 1 2009-10-25 01:18:00 1
1013 1 2009-10-25 01:19:00 2
1013 1 2009-10-25 01:20:00 3
1013 1 2009-10-25 01:21:00 4
1013 1 2009-10-25 01:22:00 5
1013 1 2009-10-25 01:23:00 6

...全文

83 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

shabble 2010-10-20

打赏
举报

[Quote=引用 3 楼 xman_78tom 的回复:]
SQL code

;with t as(
select cardid,status,time,
ROW_NUMBER() over (order by time)-
ROW_NUMBER() over (partition by status order by time) grp
from tb
)
select cardid,status,time,
(case statu……
[/Quote]

能不能对里面用到的函数主要思路做个说明，我想学习一下

shabble 2010-10-20

打赏
举报

[Quote=引用 3 楼 xman_78tom 的回复:]
SQL code

;with t as(
select cardid,status,time,
ROW_NUMBER() over (cardid,order by time)-
ROW_NUMBER() over (partition by status order by cardid,time) grp
from tb
)
select cardid,status,time,
(case statu……
[/Quote]
稍微改一下就更好了，多谢

xman_78tom 2010-10-20

打赏
举报



;with t as(

select cardid,status,time, 

ROW_NUMBER() over (order by time)-

ROW_NUMBER() over (partition by status order by time) grp

from tb

)

select cardid,status,time,

(case status when 1 

 then ROW_NUMBER() over (partition by status,grp order by time)

 else 0 end) [count]

from t order by time;

/*

1010	0	2009-10-25 01:00:00	0

1010	0	2009-10-25 01:01:00	0

1010	0	2009-10-25 01:02:00	0

1011	1	2009-10-25 01:03:00	1

1011	1	2009-10-25 01:04:00	2

1011	1	2009-10-25 01:05:00	3

1011	1	2009-10-25 01:06:00	4

1012	0	2009-10-25 01:07:00	0

1012	0	2009-10-25 01:08:00	0

1012	0	2009-10-25 01:09:00	0

1013	0	2009-10-25 01:10:00	0

1013	1	2009-10-25 01:11:00	1

1013	1	2009-10-25 01:12:00	2

1013	1	2009-10-25 01:13:00	3

1013	0	2009-10-25 01:14:00	0

1013	1	2009-10-25 01:15:00	1

1013	1	2009-10-25 01:16:00	2

1013	0	2009-10-25 01:17:00	0

1013	1	2009-10-25 01:18:00	1

1013	1	2009-10-25 01:19:00	2

1013	1	2009-10-25 01:20:00	3

1013	1	2009-10-25 01:21:00	4

1013	1	2009-10-25 01:22:00	5

1013	1	2009-10-25 01:23:00	6

*/

shabble 2010-10-20

打赏
举报

[Quote=引用 1 楼 dawugui 的回复:]
1010 0 2009-10-25 01:00:00 0
1010 0 2009-10-25 01:01:00 0
1010 0 2009-10-25 01:02:00 0

这个也符合要求?
[/Quote]

这几个的status为0，所以次数连续为1的数量一直是0，也就是只要status为零，统计的数量就为零