mysql - 如何在mysql程序中写入 "WITH CTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE"

Question

我有一个 SQL Server 过程，我需要将其转换为在 mysql 中工作。

下面是我尝试过的 Mysql 转换过程，但它不完整......

DELIMITER $$

DROP PROCEDURE IF EXISTS `SelectjqGridUsers` $$
CREATE PROCEDURE `SelectjqGridUsers` (IN PageIndex INT,IN SortColumnName VARCHAR(50),IN SortOrderBy VARCHAR(4) ,IN NumberOfRows INT ,OUT TotalRecords INT)

BEGIN

 DECLARE StartRow INT;
 DECLARE CTE VARCHAR(100);

 SELECT  TotalRecords = ( SELECT COUNT(1) FROM  Users );

 SET StartRow = (PageIndex * NumberOfRows ) + 1 ;


END $$

DELIMITER ;

这是我原来的 SQL Server 过程:

CREATE PROC [SelectjqGridUsers]
@PageIndex INT ,
@SortColumnName VARCHAR(50) ,
@SortOrderBy VARCHAR(4) ,
@NumberOfRows INT ,
@TotalRecords INT OUTPUT
AS 
BEGIN

    SET NOCOUNT ON 

    SELECT  @TotalRecords = ( SELECT    COUNT(1)
                              FROM      [Users]
                            )

    DECLARE @StartRow INT
    SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;


    WITH    CTE
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY CASE
                                                          WHEN @SortColumnName = 'UserID'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN UserID
                                                         END ASC, CASE
                                                          WHEN @SortColumnName = 'UserID'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN UserID
                                                          END DESC, CASE
                                                          WHEN @SortColumnName = 'UserName'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN UserName
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'UserName'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN UserName
                                                          END DESC, CASE
                                                          WHEN @SortColumnName = 'FirstName'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN FirstName
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'FirstName'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN FirstName
                                                          END DESC , CASE
                                                          WHEN @SortColumnName = 'MiddleName'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN MiddleName
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'MiddleName'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN MiddleName
                                                          END DESC , CASE
                                                          WHEN @SortColumnName = 'LastName'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN LastName
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'LastName'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN LastName
                                                          END DESC, CASE
                                                          WHEN @SortColumnName = 'EmailID'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN EmailID
                                                          END ASC, CASE
                                                          WHEN @SortColumnName = 'EmailID'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN EmailID
                                                          END DESC ) AS RN ,
                            UserID ,
                            UserName ,
                            FirstName ,
                            MiddleName ,
                            LastName ,
                            EmailID
                   FROM     [Users]
                 )
        SELECT  UserID ,
                UserName ,
                FirstName ,
                LastName ,
                MiddleName ,
                EmailID
        FROM    CTE
        WHERE   RN BETWEEN @StartRow - @NumberOfRows
                   AND     @StartRow - 1

    SET NOCOUNT OFF


END

有人可以帮我完成这个吗？

Answer 1

首先，MySQL 中没有直接等效的 CTE(语句开头的 WITH 子句)。也没有完全替代 ROW_NUMBER()。

但是，您可能会理解并(希望)接受这样一个事实:人类语言的句子并不总是逐字翻译为另一种语言。同样，用一种 SQL 编写的 SQL 查询可能不会也不必转换为不同类型的 for 子句。要翻译查询，您需要两件事，同样重要:了解查询的作用和在目标 SQL 品种中具有通用性。

您正在翻译的存储过程实现了特定表的动态排序行的动态分页。

对于分页，SQL Server 没有原生语法，就像 MySQL 的LIMIT ... OFFSET ... 功能一样。一个可能最典型的解决方法是使用 ROW_NUMBER() 函数和子选择(在本例中，子选择恰好采用了 CTE 的形式)，您也可以在这里看到。 。这是如何运作的？ ROW_NUMBER() 根据特定的排序条件为子查询中的每一行分配数字，主查询只需过滤这些数字即可获取范围的行，从而有效地给出你一个页面。

在 MySQL 中您不需要任何此类内容，只是因为它为您提供了 LIMIT ... 和 OFFSET ...，这两者在上下文中使用时存储过程的，可以接受整数类型参数或局部整数类型变量作为其参数(当然，它们可以 if you are using MySQL 5.5.6+ )。

至于动态排序，在 SQL Server 中，排序必须应用于 ROW_NUMBER() 函数，因为它与动态分页结合使用。但在 MySQL 中，您只需将 ROW_NUMBER() 的 ORDER BY 子句直接移至(主)查询即可。

所以，最后，这就是我想出的方法来替代存储过程中的 SELECT 查询:

SELECT  UserID ,
        UserName ,
        FirstName ,
        LastName ,
        MiddleName ,
        EmailID

FROM    Users

ORDER BY
        CASE WHEN @SortColumnName = 'UserID'     AND @SortOrderBy = 'asc'  THEN UserID     END ASC,
        CASE WHEN @SortColumnName = 'UserID'     AND @SortOrderBy = 'desc' THEN UserID     END DESC,
        CASE WHEN @SortColumnName = 'UserName'   AND @SortOrderBy = 'asc'  THEN UserName   END ASC,
        CASE WHEN @SortColumnName = 'UserName'   AND @SortOrderBy = 'desc' THEN UserName   END DESC,
        CASE WHEN @SortColumnName = 'FirstName'  AND @SortOrderBy = 'asc'  THEN FirstName  END ASC,
        CASE WHEN @SortColumnName = 'FirstName'  AND @SortOrderBy = 'desc' THEN FirstName  END DESC,
        CASE WHEN @SortColumnName = 'MiddleName' AND @SortOrderBy = 'asc'  THEN MiddleName END ASC,
        CASE WHEN @SortColumnName = 'MiddleName' AND @SortOrderBy = 'desc' THEN MiddleName END DESC,
        CASE WHEN @SortColumnName = 'LastName'   AND @SortOrderBy = 'asc'  THEN LastName   END ASC,
        CASE WHEN @SortColumnName = 'LastName'   AND @SortOrderBy = 'desc' THEN LastName   END DESC,
        CASE WHEN @SortColumnName = 'EmailID'    AND @SortOrderBy = 'asc'  THEN EmailID    END ASC,
        CASE WHEN @SortColumnName = 'EmailID'    AND @SortOrderBy = 'desc' THEN EmailID    END DESC

LIMIT   NumberOfRows
OFFSET  StartRow
;

不过，有一个问题。 LIMIT 和 OFFSET 的参数化只能以单个引用(对参数或变量)的形式完成，即您不能使用那里有一个表达式。因此，我不得不简单地使用上面的 OFFSET StartRow 。但是，它会为您提供与 SQL Server 版本返回的页面不同的页面，除非您还像这样更改 SET StartRow 语句:

SET StartRow = (PageIndex - 1) * NumberOfRows + 1 ;