--这样统计就行了select count(*) from(
select * from t1 a
where not exists(select 1 from t1 where tuserno=a.tuserno and tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a
where tTime BETWEEN '2001-01-01 00:00:01' AND '2001-12-01 23:59:59'
and ntype='A'
select * from t1 a
where not exists(select 1 from t1 where tuserno=a.tuserno and tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a
where tTime BETWEEN '2001-01-01 00:00:01' AND '2001-12-01 23:59:59'
and ntype='A'
declare @T1 table(tid int,tuserno varchar(6),tname varchar(10),tTime datetime,nType varchar(4))
insert into @t1
select 1,'00002','小章','2001-10-10 12:12:00','A'
union all select 2,'00002','小章','2001-10-10 12:13:00','A'
union all select 3,'00002','小章','2001-10-10 12:14:00','A'
union all select 3,'00002','小章','2001-10-10 12:17:00','A'
union all select 4,'00008','小王','2001-10-10 12:12:00','B'--数据统计--指定条件统计
select count(*) from(
select * from @t1 a
where not exists(select 1 from @t1 where tuserno=a.tuserno and tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a
where tTime BETWEEN '2001-01-01 00:00:01' AND '2001-12-01 23:59:59'
and ntype='A'--数据统计--分组全部统计
select ntype,count(*) from(
select * from @t1 a
where not exists(select 1 from @t1 where tuserno=a.tuserno and tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a group by ntype/*--测试结果
--数据统计--指定条件统计
-----------
2(所影响的行数为 1 行)--数据统计--分组全部统计
ntype
----- -----------
A 2
B 1(所影响的行数为 2 行)
--*/
1 小张 2001-10-10 12:12:00
2 小张 2001-10-10 12:13:00
3 小张 2001-10-10 12:14:01
4 小王 2001-10-10 12:12:00
5 小李 2001-10-10 12:13:00
6 小刘 2001-10-10 12:14:01现在需要统计一段时间内所有人的消费次数总和,
但如果是某一个人的前后两次消费时间间隔《=1分中,
则只算一次消费。
select * from t1 a
where not exists(select 1 from t1 where tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a
where ttime between '2001-01-01 00:00:00' and '2002-11-30 23:59:59'
group by tname
declare @T1 table(tid int,tname varchar(10),tTime datetime)
insert into @t1
select 1,'小张','2001-10-10 12:12:00' --这里与下面是一次
union all select 2,'小张','2001-10-10 12:13:00'
union all select 3,'小张','2001-10-10 12:14:01' --这里与前面相差超过了1分钟
union all select 4,'小王','2001-10-10 12:12:00'
union all select 5,'小李','2001-10-10 12:13:00'
union all select 6,'小刘','2001-10-10 12:14:01'--数据统计--分组全部统计
select tname,消费次数=count(*) from(
select * from @t1 a
where not exists(select 1 from @t1 where tname=a.tname
and tid<a.tid and datediff(ss,ttime,a.ttime)<=60)
) a
where ttime between '2001-01-01 00:00:00' and '2002-11-30 23:59:59'
group by tname/*--测试结果
tname
---------- -----------
小李 1
小刘 1
小王 1
小张 2(所影响的行数为 4 行)
--*/
select 1,'小张','2001-10-10 12:12:00' --这里算一次
union all select 2,'小张','2001-10-10 12:13:00' --这里不算因为<=60秒
union all select 3,'小张','2001-10-10 12:14:00' --这里也应该算一次,因为在前两
--次中取的是小的一个即1号记录
union all select 4,'小张','2001-10-10 12:15:00' --过滤
union all select 5,'小张','2001-10-10 12:16:00' --算一次
union all select 6,'小张','2001-10-10 12:17:00' -过滤
然后,对于同一人过滤掉前后两次时间间隔<=60秒的记录。
最后,用Count(*)分组统计!
就是在第二步不知道怎么做
declare @n0 varchar(5),@tm0 datetime,@tp0 char
declare @i int,@n varchar(5),@tm datetime,@tp charcreate table #t (tid int)
insert into #t select top 1 tid from t1 order by tuserno,ntype,ttimedeclare curA cursor
for
select tid,tuserno,ntype,ttime from t1 order by tuserno,ntype,ttimeopen curAfetch next from curA into @i,@n0,@tp0,@tm0
fetch next from curA into @i,@n,@tp,@tm
while @@fetch_status=0
begin
if @n=@n0 and @tp=@tp0 and datediff(n,@tm0,@tm)<=1
begin
set @i=@i
end
else
begin
insert into #t values (@i)
set @n0=@n
set @tp0=@tp
set @tm0=@tm
end
fetch next from curA into @i,@n,@tp,@tm
endclose curA
deallocate curAselect a.* from t1 a join #t b on a.tid=b.tiddrop table #t
2 00002 2001-10-10 12:13:00 A
3 00002 2001-10-10 12:14:00 A
4 00008 2001-10-10 12:12:00 A
5 00008 2001-10-10 12:13:00 b
6 00002 2001-10-10 12:15:00 A
7 00002 2001-10-10 12:15:00 b运行结果:
1 00002 2001-10-10 12:12:00 A
3 00002 2001-10-10 12:14:00 A
4 00008 2001-10-10 12:12:00 A
5 00008 2001-10-10 12:13:00 b
7 00002 2001-10-10 12:15:00 b
declare @T1 table(tid int,tname varchar(10),tTime datetime)
insert into @t1
select 1,'小张','2001-10-10 12:12:00' --这里算一次
union all select 2,'小张','2001-10-10 12:13:00' --这里不算因为<=60秒
union all select 3,'小张','2001-10-10 12:14:00' --这里也应该算一次,因为在前两
--次中取的是小的一个即1号记录
union all select 4,'小张','2001-10-10 12:15:00' --过滤
union all select 5,'小张','2001-10-10 12:16:00' --算一次
union all select 6,'小张','2001-10-10 12:17:00' --过滤
union all select 7,'小王','2001-10-10 12:12:00'
union all select 8,'小李','2001-10-10 12:13:00'
union all select 9,'小刘','2001-10-10 12:14:01'--数据统计--分组全部统计
select tname,次数=count(*)
from @t1 a
where isnull((select sum(1) from @t1 a1 where tname=a.tname and tid<a.tid
and exists(select 1 from @t1 where tname=a.tname and tid<a.tid
and datediff(ss,ttime,a.ttime)<=60)),0) %2=0
group by tname/*--测试结果tname 次数
---------- -----------
小李 1
小刘 1
小王 1
小张 3(所影响的行数为 4 行)
--*/
declare @n0 varchar(5),@tm0 datetime,@tp0 char
declare @i int,@n varchar(5),@tm datetime,@tp charcreate table #t (tid int)
insert into #t select top 1 tid from t1 order by tuserno,ntype,ttimedeclare curA cursor
for
select tid,tuserno,ntype,ttime from t1 order by tuserno,ntype,ttimeopen curAfetch next from curA into @i,@n0,@tp0,@tm0
fetch next from curA into @i,@n,@tp,@tm
while @@fetch_status=0
begin
if not (@n=@n0 and @tp=@tp0 and datediff(n,@tm0,@tm)<=1)
begin
insert into #t values (@i)
set @n0=@n
set @tp0=@tp
set @tm0=@tm
end
fetch next from curA into @i,@n,@tp,@tm
endclose curA
deallocate curAselect ntype,count(ntype) from t1 a join #t b on a.tid=b.tid group by a.ntypedrop table #t
2 00002 2001-10-10 12:13:00 A
3 00002 2001-10-10 12:14:00 A
4 00008 2001-10-10 12:12:00 A
5 00008 2001-10-10 12:13:00 b
6 00002 2001-10-10 12:15:00 A
7 00002 2001-10-10 12:15:00 b运行结果:
A 3
b 2
刚开始还不太明白意思。看来功底太差,。
感慨修行中…………………………