SQL Server 计算不连续时间间隔

最新推荐文章于 2024-08-28 04:07:04 发布

归途醉染

最新推荐文章于 2024-08-28 04:07:04 发布

阅读量377

点赞数

分类专栏： SQL 文章标签：数据库 sql SQL Server

本文链接：https://blog.csdn.net/qq_36088602/article/details/127407331

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

如图1:
在这里插入图片描述

	SELECT shengChanHao AS shengchao,addDate 
	INTO #temp
	FROM packBanCPinUR WHERE 
	CONVERT(VARCHAR(10),addDate,23) BETWEEN convert(char(10),'2022-10-17',120) and convert(char(10),'2022-10-17',120) 
	AND classCode='L1'
	 GROUP BY shengChanHao,addDate ORDER BY addDate


SELECT shengchao, Convert(decimal(10,2),CONVERT(FLOAT,a.diff/60.0))  FROM 
(
	SELECT shengchao,sum(diff) as diff from (
     select shengchao, datediff(minute,min(adddate), max(adddate) ) as diff  ,(seqnum - seqnum2) as seqNum  from(
            select shengchao,adddate,row_number() over (order by adddate) as seqnum,
		        row_number() over (partition by shengchao order by adddate) as seqnum2 from #temp
   
		  )s	group by  shengchao ,(seqnum - seqnum2)
		)a group by shengchao
)a