PHP PDO MSSQL ROW_NUMBER() error

本文关键字：error NUMBER PDO MSSQL ROW PHP | 更新日期: 2023-09-27

我使用php pdo与SQL Server原生客户端11.0作为驱动程序。显然，我不能使用LIMIT子句，因为它是专有的，并且在试图解决选择行范围的问题时，我一直遇到问题。

我的设置如下:

$statement = "SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  CASE.no, CASE.type, CASE.date,    CASE.ju, USERS.name, TBRLMF.rd_rl_description FROM dbo.CASE INNER JOIN dbo.USERS ON dbo.CASE.no = dbo.USERS.case_no INNER JOIN dbo.CMSTBRLMF ON dbo.USERS.relationship = dbo.TBRLMF.code ";
if($exact == 'checked'){
$exact = '=';
}else{
$exact = 'LIKE';
}
if($searchtype == 'users'){
$statement .= " WHERE USERS.name $exact '%$searchstring%'";
}else{
$statement .= " WHERE USERS.no $exac '%$searchstring%'";
}
$statement .= " AND RowNum BETWEEN :offset AND :max";
$statement  = $dbh->prepare($statement);
$statement->bindParam(':offset', $offset, PDO::PARAM_INT);
$statement->bindParam(':max', $max, PDO::PARAM_INT);

，只要不包含这行代码:

$statement .= " AND RowNum BETWEEN :offset AND :max";

它工作良好，但给我所有的数据。

当包含这行时，我收到以下错误:-

捕获异常'PDOException' with message 'SQLSTATE[42S22]: Column not found: 207 [Microsoft][SQL Server Native Client 11.0][SQL Server]无效的列名'RowNum'。(SQLExecute[207] at ext'pdo_odbc'odbc_stmt.c:254)' in C:'pub'test'Classes'Core.php:44

我很茫然，MSSQL对我来说是新的。

非常感谢。

该列上的谓词不能在WHERE子句中。(WHERE子句中的谓词在访问行时求值;该表达式(解析函数)的值在访问行之后才可用。

你可以在HAVING子句中引用列别名:

" HAVING RowNum BETWEEN ... ";

你不能在WHERE子句中使用COLUMN ALIAS(在你的情况下是RowNum)。不如使用派生表更改查询，如下所示

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  
CASE.no, 
CASE.type, 
CASE.date,    
CASE.ju, 
USERS.name, 
TBRLMF.rd_rl_description 
FROM dbo.CASE 
INNER JOIN dbo.USERS 
ON dbo.CASE.no = dbo.USERS.case_no 
INNER JOIN dbo.CMSTBRLMF 
ON dbo.USERS.relationship = dbo.TBRLMF.code 
WHERE USERS.name LIKE '%string%'
AND USERS.no LIKE '%string%'
) X 
WHERE RowNum BETWEEN 1 AND 10;

编辑:

您也可以使用CTE(公共表表达式)来解决这个问题，如下所示

创建CTE

WITH NEWCTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  
    CASE.no, 
    CASE.type, 
    CASE.date,    
    CASE.ju, 
    USERS.name, 
    TBRLMF.rd_rl_description 
    FROM dbo.CASE 
    INNER JOIN dbo.USERS 
    ON dbo.CASE.no = dbo.USERS.case_no 
    INNER JOIN dbo.CMSTBRLMF 
    ON dbo.USERS.relationship = dbo.TBRLMF.code 
    WHERE USERS.name LIKE '%searchstring%'
    AND USERS.no LIKE '%searchstring%'
)

查询CTE

SELECT * 
FROM NEWCTE
WHERE RowNum BETWEEN 1 AND 10