基于写集的查询/查询/子查询而不是游标 [英] write set based query /query/sub query instead of cursor

问题描述

DECLARE @Sno INT
DECLARE @Id INT
DECLARE @RSUM INT
DECLARE @TotalMinutes int 
DECLARE @OverTimeMinutes int
DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT EmpId,Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      SELECT @RSUM = SUMinutes  FROM #tempTbl WHERE SNo=@Sno
      SELECT @TotalMinutes = SUM(SUMinutes) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
      IF(@TotalMinutes>2400)
         BEGIN
            IF(SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OvertimeMinutes>0)>0
               BEGIN
                  SELECT @OverTimeMinutes = SUMinutes FROM #tempTbl WHERE SNo=@Sno
                  SET @RSUM = 0
               END
            ELSE
               BEGIN
                  SET @OverTimeMinutes = (@TotalMinutes-2400)
                  SET @RSUM = (1000 - @OverTimeMinutes)
               END
         END
      ELSE
         BEGIN
            SET @OverTimeMinutes = 0
         END
								   
      --Calculate Employee Overtime Hours - End
      UPDATE #tempTbl SET OvertimeMinutes = @OverTimeMinutes, RSUM = @RSUM WHERE SNo=@Sno
      SET @OverTimeMinutes = 0
      SET @TotalMinutes = 0
      SET @RSUM = 0
      FETCH NEXT FROM sumcal INTO @Id,@Sno
   END
CLOSE sumcal
DEALLOCATE sumcal

推荐答案

这不是解决方案，而是对必要的内容进行重新审视从已编辑的OP代码中获取输出

This is not the solution but a rehash of what is necessary to get output from what the edited OP code

CREATE TABLE #tempTbl( [Sno][int], [Id][int], [RSUM][int], [SUMinutes][int], [OverTimeMinutes][int])

表数据data_MZ_（td）.txt内容

And the table data "data_MZ_(td).txt" content

1	12001	0	430	500
2	12002	0	444	500
3	12003	10	200	30
4	12004	20	200	200
5	12005	0	400	200
6	12006	0	300	2000
7	12007	0	200	456
8	12008	0	200	34562
9	12009	0	200	34523
10	12010	0	200	563456

输入数据

Input data

BULK INSERT #tempTbl
	FROM 'C:\Users\MZ\data_MZ_(td).txt'

查询#tempTbl现在显示在表格中

Query on #tempTbl now shows in table

SELECT * FROM #tempTbl

As

As

Sno	Id	RSUM	SUMinutes	OverTimeMinutes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	12001	0	430	500
2	12002	0	444	500
3	12003	10	200	30
4	12004	20	200	200
5	12005	0	400	200
6	12006	0	300	2000
7	12007	0	200	456
8	12008	0	200	34562
9	12009	0	200	34523
10	12010	0	200	563456

SOOP

The SOOP

DECLARE @Sno INT
DECLARE @Id INT
DECLARE @RSUM INT
DECLARE @TotalMinutes int 
DECLARE @OverTimeMinutes int
--DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT EmpId,Sno FROM #tempTbl
DECLARE sumcal CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR SELECT Id, Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
--FETCH FIRST FROM sumcal INTO @Sno

WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      SELECT @RSUM = SUMinutes  FROM #tempTbl WHERE SNo=@Sno
      SELECT @TotalMinutes = SUM(SUMinutes) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
      IF(@TotalMinutes>2400)
         BEGIN
            IF(SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OvertimeMinutes>0)>0
               BEGIN
                  SELECT @OverTimeMinutes = SUMinutes FROM #tempTbl WHERE SNo=@Sno
                  SET @RSUM = 0
               END
            ELSE
               BEGIN
                  SET @OverTimeMinutes = (@TotalMinutes-2400)
                  SET @RSUM = (1000 - @OverTimeMinutes)
               END
         END
      ELSE
         BEGIN
            SET @OverTimeMinutes = 0
         END
								   
--      Calculate Employee Overtime Hours - End
      UPDATE #tempTbl SET OvertimeMinutes = @OverTimeMinutes, RSUM = @RSUM WHERE SNo=@Sno
      SET @OverTimeMinutes = 0
      SET @TotalMinutes = 0
      SET @RSUM = 0
      FETCH NEXT FROM sumcal INTO @Id,@Sno
      --FETCH NEXT FROM sumcal INTO @Sno
   END
CLOSE sumcal
DEALLOCATE sumcal

抓取器之后的另一个查询SELECT * FROM #tempTbl

Another query after the fetcher does its thing 'SELECT * FROM #tempTbl'

Sno	Id	RSUM	SUMinutes	OverTimeMinutes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	12001	430	430	0
2	12002	444	444	0
3	12003	200	200	0
4	12004	200	200	0
5	12005	400	400	0
6	12006	300	300	0
7	12007	200	200	0
8	12008	200	200	0
9	12009	200	200	0
10	12010	200	200	0

这可能不是OP正在寻找的输出。没有任何进一步的信息，遇到确切的错误，一些好的输出（预期的）等等，这是不可能的。

This couldn't possibly be the output for which the OP is looking. Without any further information, exact errors encountered, some "good" output (expected), etc it's impossible to proceed on this matter.

这篇关于基于写集的查询/查询/子查询而不是游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！