数据库表a 类似如下
taskid name time status
1 a t1 开始
1 a t2 结束
1 b t3 开始
1 b t4 结束
1 a t5 开始
1 a t6 结束
…
我想求出name 为a和b的 的以开始时间和结束时间为起止点的工作时间和
taskid name time status
1 a t1 开始
1 a t2 结束
1 b t3 开始
1 b t4 结束
1 a t5 开始
1 a t6 结束
…
我想求出name 为a和b的 的以开始时间和结束时间为起止点的工作时间和
t.name,sum(datediff(hh,t.time,t.time1)) as time
from
(select
t1.name,t1.time,min(t2.time) as time2
from
a t1,b t2
where
t1.taskid=t2.taskid
and
t1.name=t2.name
and
t1.time<=t2.time
and
t1.status='开始'
and
t2.status='结束'
group by
t1.name,t1.time) t
group by
t.name
select name,min(s) from (select name,status,count(1) as s from tab order by name,status)b
(
taskid int,
name varchar(10),
time datetime,
status varchar(5)
)
insert into tb
select 1,'a','2007-01-01','开始' union all
select 1,'a','2007-01-02','结束' union all
select 1,'b','2007-01-01','开始' union all
select 1,'b','2007-01-02','结束' union all
select 1,'a','2007-01-03','开始' union all
select 1,'a','2007-01-04','结束'
go
select identity(int,1,1) id,* into # from tb
go
select name1,sum(datediff(day,time1,time2)) sumdate from
(
select max(taskid) taskid,max(name) name1,max(time) time1,max(status) status,max(taskid2) taskid2,max(name2) name2,max(time2) time2, max(status2) status2 from
(
select
case when id%2=1 then taskid end taskid,
case when id%2=1 then [name] end [name],
case when id%2=1 then [time] end [time],
case when id%2=1 then status end status,case when id%2=0 then taskid end taskid2,
case when id%2=0 then [name] end name2,
case when id%2=0 then [time] end time2,
case when id%2=0 then status end status2,
(id-1)/2 as grp
from #
) a group by grp
)b group by name1
--结果
name1 sumdate
---------- -----------
a 2
b 1