SQL临时表|游标|两个日期之间计算时差|临时表条件查询

DECLARE @ExceptionLocationId CHAR(12)='',
@ExceptionNumber CHAR(12)='',
@AssetName NVARCHAR(100)='',
@WorkcenterId NVARCHAR(100)='',
@ExceptionTypeName NVARCHAR(100)='',
@ProductionExceptionManagementId CHAR (12)  --主表主键ID

   ---创建临时表
  --------创建故障维修记录临时表-------------------------------
        IF OBJECT_ID('tempdb..#TB') IS NOT NULL

        DROP TABLE #TB
        
        CREATE TABLE #TB(
        字段1 NVARCHAR(100),      ---场地
        字段2 NVARCHAR(200), --生产线
        字段3 NVARCHAR(200),  --设备名称
        
        )
   ----定义游标---
    DECLARE PemID_cursor CURSOR LOCAL
        FOR
        SELECT ProductionExceptionManagementId FROM dbo.ProductionExceptionManagement WHERE ExceptionStatusId<>''  ---状态不能为空
        FOR READ ONLY
        ---打开游标
        OPEN PemID_cursor
        ---读取数据
        FETCH NEXT FROM PemID_cursor
        INTO @ProductionExceptionManagementId
        WHILE @@FETCH_STATUS=0
        BEGIN

    SELECT 
    @ExceptionLocationId = ExceptionLocationId 
                            FROM ExceptionLocation Ex LEFT OUTER JOIN  dbo.ProductionExceptionManagement Pd ON Ex.FactoryId=pd.FactoryId AND Ex.SiteId=Pd.SiteId
                            AND EX.WorkcenterId=Pd.WorkcenterId WHERE Ex.ExceptionTypeId=Pd.ExceptionTypeId AND ProductionExceptionManagementId=@ProductionExceptionManagementId
    -----查找单号-----------------
     SET @ExceptionNumber=(SELECT ExceptionNumber FROM dbo.ProductionExceptionManagement WHERE ProductionExceptionManagementid=@ProductionExceptionManagementId)
    
    ---主从表中需要查找匹配的字段值1，字段值2，字段值3......---

   SET @字段1=()
   SET @字段2=()
  ...........

 --- XCWaitTime AS 现场恢复等待时长
     --SET @XCWaitTime=(SELECT DATEDIFF(MINUTE,@FailureTime,@XCHandleTime))
    SET @XCWaitTime=CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @FailureTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒' 
    ---现场恢复等待时长----
    ---MaintenanceDuration AS 修护课维修时长
    ---SET  @MaintenanceDuration=(SELECT DATEDIFF(MINUTE,@HandleTime,@XCHandleTime))
    SET @MaintenanceDuration=CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF ( ss, @HandleTime, @XCHandleTime ) % 60 AS INT ) AS VARCHAR ) + '秒' 
    ---修护课维修时长
    ---主从表中需要查找匹配的字段值1，字段值2，字段值3......
      --------临时表插入值-------------------------------
     INSERT INTO #TB    (
            字段1 ,
            字段2 , 
            字段3,
            .....
            )
            SELECT  
            @字段1 ,
            @字段2 , 
            @字段3 
            .....
            
          --进入下一行数据--
            
            FETCH NEXT FROM PemID_cursor
            INTO @ProductionExceptionManagementId
             
            END
            ---关闭游标--------------
            CLOSE PemID_cursor
            ---释放游标------------------
            DEALLOCATE PemID_cursor 

      ----统计展示重复数据次数前五----

           SELECT TOP 5 字段1,COUNT(0) AS 次数  FROM #TB WHERE 字段2='自动化D/C1线' GROUP BY 字段1 HAVING COUNT(字段1)>1  ORDER BY 次数 DESC 

        ----设备停机时长----
           SELECT   TOP 5 字段1 AS 名称1,  字段3 AS 名称3 FROM #TB   WHERE  字段2='自动化D/C4线' ORDER BY 字段2 desc

       ----删除临时表---

          DROP TABLE #TB