首页 手机软件 手机游戏 单机游戏 资讯文章 专题下载 游戏合集 文章合集 php下载 php教程 电脑软件
+ -
当前位置：首页 → 问答吧 → 请教关于SQL统计相同编号NO时间累计的方法，内有示例数据

请教关于SQL统计相同编号NO时间累计的方法，内有示例数据

时间：2011-12-19

来源：互联网

SQL code 

create table #Test(
ID int ,sNo varchar(15),dtDate datetime)

insert into #Test(id,sno,dtdate) values(1,'1','2011-12-19 10:10:10')
insert into #Test(id,sno,dtdate) values(2,'1','2011-12-19 10:10:40')
insert into #Test(id,sno,dtdate) values(3,'1','2011-12-19 10:11:10')
insert into #Test(id,sno,dtdate) values(4,'1','2011-12-19 10:11:40')
insert into #Test(id,sno,dtdate) values(5,'1','2011-12-19 10:12:10')
insert into #Test(id,sno,dtdate) values(6,'1','2011-12-19 10:12:40')
insert into #Test(id,sno,dtdate) values(7,'1','2011-12-19 10:13:10')
insert into #Test(id,sno,dtdate) values(8,'1','2011-12-19 10:14:10')
insert into #Test(id,sno,dtdate) values(9,'1','2011-12-19 10:15:10')
insert into #Test(id,sno,dtdate) values(10,'2','2011-12-19 10:16:10')
insert into #Test(id,sno,dtdate) values(11,'2','2011-12-19 10:17:10')
insert into #Test(id,sno,dtdate) values(12,'2','2011-12-19 10:18:10')
insert into #Test(id,sno,dtdate) values(13,'2','2011-12-19 10:19:10')
insert into #Test(id,sno,dtdate) values(14,'1','2011-12-19 10:20:10')
insert into #Test(id,sno,dtdate) values(15,'1','2011-12-19 10:21:10')
insert into #Test(id,sno,dtdate) values(16,'1','2011-12-19 10:22:10')
insert into #Test(id,sno,dtdate) values(17,'2','2011-12-19 10:23:10')
insert into #Test(id,sno,dtdate) values(18,'2','2011-12-19 10:24:10')
insert into #Test(id,sno,dtdate) values(19,'2','2011-12-19 10:25:10')
 
select * from #Test 
drop table #Test

我想统计出sNO为1的时间段总和，为2的时间段总和。

作者: dazuo0312   发布时间: 2011-12-19

也就是想统计出
编号 开始时间 结束时间 累计时间
1 2011-12-19 10:10:10 2011-12-19 10:15:10 5分钟
1 2011-12-19 10:20:10 2011-12-19 10:22:10 7分钟
2 类似

作者: dazuo0312   发布时间: 2011-12-19

SQL code 

select sno,min(dtdate) as stdate,max(stdate) as endate,
    datediff(mi,min(dtdate),max(dtdate)) as ldate
from tb
group by sno

作者: AcHerat   发布时间: 2011-12-19

SQL code 

create table #Test(
ID int ,sNo varchar(15),dtDate datetime)
insert into #Test(id,sno,dtdate) values(1,'1','2011-12-19 10:10:10')
insert into #Test(id,sno,dtdate) values(2,'1','2011-12-19 10:10:40')
insert into #Test(id,sno,dtdate) values(3,'1','2011-12-19 10:11:10')
insert into #Test(id,sno,dtdate) values(4,'1','2011-12-19 10:11:40')
insert into #Test(id,sno,dtdate) values(5,'1','2011-12-19 10:12:10')
insert into #Test(id,sno,dtdate) values(6,'1','2011-12-19 10:12:40')
insert into #Test(id,sno,dtdate) values(7,'1','2011-12-19 10:13:10')
insert into #Test(id,sno,dtdate) values(8,'1','2011-12-19 10:14:10')
insert into #Test(id,sno,dtdate) values(9,'1','2011-12-19 10:15:10')
insert into #Test(id,sno,dtdate) values(10,'2','2011-12-19 10:16:10')
insert into #Test(id,sno,dtdate) values(11,'2','2011-12-19 10:17:10')
insert into #Test(id,sno,dtdate) values(12,'2','2011-12-19 10:18:10')
insert into #Test(id,sno,dtdate) values(13,'2','2011-12-19 10:19:10')
insert into #Test(id,sno,dtdate) values(14,'1','2011-12-19 10:20:10')
insert into #Test(id,sno,dtdate) values(15,'1','2011-12-19 10:21:10')
insert into #Test(id,sno,dtdate) values(16,'1','2011-12-19 10:22:10')
insert into #Test(id,sno,dtdate) values(17,'2','2011-12-19 10:23:10')
insert into #Test(id,sno,dtdate) values(18,'2','2011-12-19 10:24:10')
insert into #Test(id,sno,dtdate) values(19,'2','2011-12-19 10:25:10')

select sno,min(dtdate) as stdate,max(dtdate) as endate,
    datediff(mi,min(dtdate),max(dtdate)) as ldate
from #Test
group by sno

drop table #Test

/************************

sno             stdate                  endate                  ldate
--------------- ----------------------- ----------------------- -----------
1               2011-12-19 10:10:10.000 2011-12-19 10:22:10.000 12
2               2011-12-19 10:16:10.000 2011-12-19 10:25:10.000 9

(2 行受影响)

作者: AcHerat   发布时间: 2011-12-19

SQL code 

create table #Test(
ID int ,sNo varchar(15),dtDate datetime)
insert into #Test(id,sno,dtdate) values(1,'1','2011-12-19 10:10:10')
insert into #Test(id,sno,dtdate) values(2,'1','2011-12-19 10:10:40')
insert into #Test(id,sno,dtdate) values(3,'1','2011-12-19 10:11:10')
insert into #Test(id,sno,dtdate) values(4,'1','2011-12-19 10:11:40')
insert into #Test(id,sno,dtdate) values(5,'1','2011-12-19 10:12:10')
insert into #Test(id,sno,dtdate) values(6,'1','2011-12-19 10:12:40')
insert into #Test(id,sno,dtdate) values(7,'1','2011-12-19 10:13:10')
insert into #Test(id,sno,dtdate) values(8,'1','2011-12-19 10:14:10')
insert into #Test(id,sno,dtdate) values(9,'1','2011-12-19 10:15:10')
insert into #Test(id,sno,dtdate) values(10,'2','2011-12-19 10:16:10')
insert into #Test(id,sno,dtdate) values(11,'2','2011-12-19 10:17:10')
insert into #Test(id,sno,dtdate) values(12,'2','2011-12-19 10:18:10')
insert into #Test(id,sno,dtdate) values(13,'2','2011-12-19 10:19:10')
insert into #Test(id,sno,dtdate) values(14,'1','2011-12-19 10:20:10')
insert into #Test(id,sno,dtdate) values(15,'1','2011-12-19 10:21:10')
insert into #Test(id,sno,dtdate) values(16,'1','2011-12-19 10:22:10')
insert into #Test(id,sno,dtdate) values(17,'2','2011-12-19 10:23:10')
insert into #Test(id,sno,dtdate) values(18,'2','2011-12-19 10:24:10')
insert into #Test(id,sno,dtdate) values(19,'2','2011-12-19 10:25:10')

;with ach as
(
    select sno,min(dtdate) as stdate,max(dtdate) as endate,
        datediff(mi,min(dtdate),max(dtdate)) as ldate,
        px=row_number() over (order by getdate())
    from (select *,rid=row_number() over (partition by sno order by id) from #Test) t
    group by (id-rid),sno
)

select sno,stdate,endate,
    (select sum(ldate) from ach where sno=t.sno and px<=t.px) as ldate
from ach t

drop table #Test

/*******************************

sno             stdate                  endate                  ldate
--------------- ----------------------- ----------------------- -----------
1               2011-12-19 10:10:10.000 2011-12-19 10:15:10.000 5
1               2011-12-19 10:20:10.000 2011-12-19 10:22:10.000 7
2               2011-12-19 10:16:10.000 2011-12-19 10:19:10.000 3
2               2011-12-19 10:23:10.000 2011-12-19 10:25:10.000 5

(4 行受影响)

作者: AcHerat   发布时间: 2011-12-19

SQL code 
create table #Test(
ID int ,sNo varchar(15),dtDate datetime)

insert into #Test(id,sno,dtdate) values(1,'1','2011-12-19 10:10:10')
insert into #Test(id,sno,dtdate) values(2,'1','2011-12-19 10:10:40')
insert into #Test(id,sno,dtdate) values(3,'1','2011-12-19 10:11:10')
insert into #Test(id,sno,dtdate) values(4,'1','2011-12-19 10:11:40')
insert into #Test(id,sno,dtdate) values(5,'1','2011-12-19 10:12:10')
insert into #Test(id,sno,dtdate) values(6,'1','2011-12-19 10:12:40')
insert into #Test(id,sno,dtdate) values(7,'1','2011-12-19 10:13:10')
insert into #Test(id,sno,dtdate) values(8,'1','2011-12-19 10:14:10')
insert into #Test(id,sno,dtdate) values(9,'1','2011-12-19 10:15:10')
insert into #Test(id,sno,dtdate) values(10,'2','2011-12-19 10:16:10')
insert into #Test(id,sno,dtdate) values(11,'2','2011-12-19 10:17:10')
insert into #Test(id,sno,dtdate) values(12,'2','2011-12-19 10:18:10')
insert into #Test(id,sno,dtdate) values(13,'2','2011-12-19 10:19:10')
insert into #Test(id,sno,dtdate) values(14,'1','2011-12-19 10:20:10')
insert into #Test(id,sno,dtdate) values(15,'1','2011-12-19 10:21:10')
insert into #Test(id,sno,dtdate) values(16,'1','2011-12-19 10:22:10')
insert into #Test(id,sno,dtdate) values(17,'2','2011-12-19 10:23:10')
insert into #Test(id,sno,dtdate) values(18,'2','2011-12-19 10:24:10')
insert into #Test(id,sno,dtdate) values(19,'2','2011-12-19 10:25:10')

--drop table #Test

with cte as
(
select 编号=sNo,
      开始时间=MIN(dtDate),
      结束时间=MAX(dtDate),
      累计时间=DATEDIFF(MI,MIN(dtDate),MAX(dtDate)),
      no=ROW_NUMBER() over(PARTITION by sNo order by MIN(dtDate))
from #Test a group by sNo,CONVERT(varchar(15),dtDate,120))
select 编号,开始时间,结束时间,
       累计时间=(select SUM(累计时间) from cte where 编号=a.编号 and no<=a.no)
from cte a       

/*
编号              开始时间                    结束时间                    累计时间
--------------- ----------------------- ----------------------- -----------
1               2011-12-19 10:10:10.000 2011-12-19 10:15:10.000 5
1               2011-12-19 10:20:10.000 2011-12-19 10:22:10.000 7
2               2011-12-19 10:16:10.000 2011-12-19 10:19:10.000 3
2               2011-12-19 10:23:10.000 2011-12-19 10:25:10.000 5

(4 行受影响)

作者: ssp2009   发布时间: 2011-12-19

相关阅读 更多

热门阅读

热门下载

更多
关于本站 免责申明 联系我们

Copyright 2006-2020 php爱好者(phpfans.net) All Rights Reserved.备案号：湘ICP备2023016114号-2

本站为非盈利性网站，不接受任何广告。本站所有资源均由网友上传，如有侵权，请发邮件至 [email protected] phpfans.net