34,588
社区成员
发帖
与我相关
我的任务
分享
IF NOT OBJECT_ID('tb') IS NULL
DROP TABLE tb
create table tb(a1 int,a2 datetime,a3 varchar(50),a4 float,a5 float)
insert into tb select 801 ,'2012-2-13 18:30:00','甲',5.2 ,0.3
insert into tb select 801 ,'2012-2-14 0:00:03','甲',6.2 ,0.3
insert into tb select 801 ,'2012-2-14 6:30:05','乙', 11.6, 0.4
insert into tb select 801 ,'2012-2-14 18:30:01','甲', 5.2, 0.3
insert into tb select 801 ,'2012-2-15 0:00:03','甲', 6.3 ,0.2
insert into tb select 801 ,'2012-2-15 6:30:05','乙', 11.6 ,0.4
insert into tb select 801 ,'2012-2-15 18:30:01','甲', 6.2, 0.3
insert into tb select 801 ,'2012-2-16 0:00:03','甲', 6.1 ,0.4
with a as(
select a1,a2,a3 ,a4,a5 from tb)
, b as (
select a1,case when datepart(hour,a2)=0 then dateadd(mi,30,dateadd(hour,18,dateadd(dd,-1,a2))) else a2 end a2,a3,a4,a5 from a )
, c as (
select a2,dense_rank()over(order by dateadd(ss,-datepart(ss,a2),a2))na from b )
,d as(
select a1,dateadd(ss,-datepart(ss,a2),a2)a2,a3,sum(a4)a4,sum(a5)a5,dense_rank()over(order by dateadd(ss,-datepart(ss,a2),a2))na
from b group by a1,dateadd(ss,-datepart(ss,a2),a2),a3)
select d.a1,cc.a2,d.a3,d.a4,d.a5 from d left join (select min(a2)a2,na from c group by na)cc on cc.na=d.na
a1 a2 a3 a4 a5
----------- ----------------------- -------------------------------------------------- ---------------------- ----------------------
801 2012-02-13 18:30:00.000 甲 11.4 0.6
801 2012-02-14 06:30:05.000 乙 11.6 0.4
801 2012-02-14 18:30:01.000 甲 11.5 0.5
801 2012-02-15 06:30:05.000 乙 11.6 0.4
801 2012-02-15 18:30:01.000 甲 12.3 0.7
/*
1 801 2012-02-13 18:30:00.000 甲 5.2 0.3
3 801 2012-02-14 06:30:05.000 乙 11.6 0.4
4 801 2012-02-14 18:30:01.000 甲 5.2 0.3
6 801 2012-02-15 06:30:05.000 乙 11.6 0.4
7 801 2012-02-15 18:30:01.000 甲 6.2 0.3
8 801 2012-02-16 00:00:03.000 甲 6.1 0.4*/
with TT
as(
select ROW_NUMBER() over( order by getdate()) as no,*
from TB )
select A.*
from TT A
where no in (select max(no) as no from TT
where A.班次 = 班次
and A.机器号=机器号
group by convert(varchar(10),开始时间,120) ,班次)
/*
1 801 2012-02-13 18:30:00.000 甲 5.2 0.3
3 801 2012-02-14 06:30:05.000 乙 11.6 0.4
4 801 2012-02-14 18:30:01.000 甲 5.2 0.3
6 801 2012-02-15 06:30:05.000 乙 11.6 0.4
7 801 2012-02-15 18:30:01.000 甲 6.2 0.3
8 801 2012-02-16 00:00:03.000 甲 6.1 0.4*/
select 机器号, min(开始时间), 班次, sum(开机时间总和), sum(开机时间总和)
from tb group by 机器号, convert(varchar,dateadd(minute,-30,开始时间),112), 班次
order by 1,2
SELECT * FROM 表名 WHERE CAST(SUBSTRING(CONVERT(CHAR(8),开始时间,108),1,2) AS INT) > 0
/**
机器号 开始时间 班次 开机时间总和 关机时间总和
______________________________________________________________
801 2012-02-13 18:30:00.000 甲 5.2 0.3
801 2012-02-14 18:30:01.000 甲 5.2 0.3
801 2012-02-14 06:30:05.000 乙 11.6 0.4
801 2012-02-15 18:30:01.000 甲 6.2 0.3
801 2012-02-15 06:30:05.000 乙 11.6 0.4
**/