sql - 逐行(无光标或循环)

Question

在这里，我有具有 RollNumbers 和他们的类(class)代码的学生的示例数据。

-------------------------
    Roll      CourseCode
--------------------------
    1011       CS201
    2213       CS201
    3312       CS101
    4000       CS201
    1011       CS101
    5312       ME102
    1011       PT101
    3312      ME102

结果应该是 Coursecode和他们的考试日期

例如(整理出不同的类(class)代码)

首先，我选择 CS201 并为该类(class)代码指定日期；将它放在一个临时表中，然后我选择了 CS101 并在临时表中检查此 Coursecode 的 RollNumber 是否与临时表中其他 Coursecode 的任何其他 RollNumber 匹配。

---------------------
    Code    Date
---------------------
    CS101    1
    CS201    2
    ME102    1
    PT101    3

我的代码:

#temp3包含所有数据(CourseCodes、RollNumbers)

#mytemp1 (输出数据)

并且光标包含不同的类(class)代码

SET @cursor = CURSOR FOR
SELECT DISTINCT coursecode
FROM #temp3
ORDER BY CourseCode

OPEN @cursor
FETCH NEXT
FROM @cursor INTO @cursorid


WHILE @@FETCH_STATUS = 0
    BEGIN 

        BEGIN
            SET @j=1
            WHILE(@j !=9999999) 
            BEGIN 

                IF( SELECT COUNT(*) FROM #temp3 WHERE CourseCode = @cursorid AND RegdNo IN (
                    SELECT  RegdNo FROM #temp3 WHERE CourseCode IN ( SELECT coursecode FROM #myTemp1 WHERE counter1 = @j)
                )) = 0
                        BEGIN
                            INSERT INTO #myTemp1 VALUES (@cursorid,@j)
                            SET @j=9999999
                        END
                ELSE   
                BEGIN
                        SET @j = @j + 1
                END 
            END
    END

FETCH NEXT
FROM @cursor INTO @cursorid
END
CLOSE @cursor
DEALLOCATE @cursor

此代码工作正常，但花费了太多时间(4110222 条记录)

任何帮助，将不胜感激

Answer 1

这是一些代码。我相信您在输出和 CS101 中有错误应该在 CS201 之前:

DECLARE @t TABLE ( Roll INT, Code CHAR(5) )

INSERT  INTO @t
VALUES  ( 1011, 'CS201' ),
        ( 2213, 'CS201' ),
        ( 3312, 'CS101' ),
        ( 4000, 'CS201' ),
        ( 1011, 'CS101' ),
        ( 5312, 'ME102' ),
        ( 1011, 'PT101' ),
        ( 3319, 'ME102' );

WITH    cte1
          AS ( SELECT   code ,
                        ROW_NUMBER() OVER ( ORDER BY Code ) AS rn
               FROM     @t
               GROUP BY code
             ),
        cte2
          AS ( SELECT   code ,
                        rn ,
                        1 AS Date
               FROM     cte1
               WHERE    rn = 1
               UNION ALL
               SELECT   c1.code ,
                        c1.rn ,
                        CASE WHEN EXISTS ( SELECT   *
                                           FROM     @t a
                                                    JOIN @t b ON a.Roll = b.Roll
                                                    JOIN cte1 c ON c.rn < c1.rn
                                                              AND b.Code = c.code
                                           WHERE    a.code = c1.code ) THEN 1
                             ELSE 0
                        END
               FROM     cte1 c1
                        JOIN cte2 c2 ON c1.rn = c2.rn + 1
             ),
        cte3
          AS ( SELECT   Code ,
                        CASE WHEN Date = 0 THEN 1
                             ELSE SUM(Date) OVER ( ORDER BY rn )
                        END AS Date
               FROM     cte2
             )
    SELECT  * FROM    cte3

输出:

Code    Date
CS101   1
CS201   2
ME102   1
PT101   3

编辑:
cte1将返回:

code    rn
CS101   1
CS201   2
ME102   3
PT101   4

主要工作在 cte2 .它是递归公用表表达式。
首先，您从 cte1 中取前 1 行:

SELECT   code ,
         rn ,
         1 AS Date
         FROM     cte1
         WHERE    rn = 1

然后递归进行:

您正在加入 cte1在 cte2并选择以下 rns (2, 3...) 并检查 CS201 中是否有任何卷在第一步中匹配先前代码( CS101 )中的卷，检查 ME102 中是否有任何卷匹配在第二步等之前的代码( CS101, CS201 )中滚动。如果存在，则返回 1 否则返回 0:

code    rn  Date
CS101   1   1
CS201   2   1
ME102   3   0
PT101   4   1

最后 cte3执行以下操作:如果 Date = 0，则返回 1，否则返回包括当前行在内的前几行中的日期总和。

编辑 1:

由于我的理解不正确，这里还有一个声明:

WITH    cte
          AS ( SELECT   code ,
                        ROW_NUMBER() OVER ( ORDER BY Code ) AS rn
               FROM     @t
               GROUP BY code
             )

  SELECT co.Code,
  DENSE_RANK() OVER(ORDER BY ISNULL(o.Code, co.Code)) AS Date
           FROM cte     co
  OUTER APPLY(SELECT TOP 1 ci.Code 
              FROM cte ci 
              WHERE ci.rn < co.rn AND 
              NOT EXISTS(SELECT * FROM @t 
                         WHERE code = ci.code AND 
                         roll IN(SELECT roll FROM @t WHERE code = co.code)) ORDER BY ci.rn) o
  ORDER BY co.rn     

输出:

Code    Date
CS101   1
CS201   2
ME102   1
PT101   2

编辑2:

这很疯狂，但是，这里的代码似乎有效:

WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY roll ORDER BY Code ) AS Date
               FROM     @t
             )
    SELECT  Code ,
            MAX(Date) AS Date
    FROM    cte
    GROUP BY Code