带启动和限制的PHP mssql查询 - 追加查询问题

Question

我通常在PHP/MySQL中编写代码，但对于此项目必须使用MSSQL。

下面的逻辑可以在MySQL中工作，但是因为在MSSQL中设置限制是一个非常复杂的查询，所以当我想要附加到查询时，它会引发问题。我玩过这个查询，试图弄清楚它应该如何，但无法让它工作。请帮忙。

//get all products for shopkeeper 
    if($limit == 0) { 
     $prod_qry = "SELECT id, productName FROM products WITH(NOLOCK) WHERE shopkeeper = '$this->shopkeeper'"; 
    } 
    if($limit > 0) { 
     $prod_qry = "SELECT * FROM (SELECT id, productName, ROW_NUMBER() OVER (ORDER BY id) as row FROM products WITH(NOLOCK) WHERE shopkeeper='$this->shopkeeper' ) a WHERE row > $start and row <= $limit"; 
    } 
    if($hidden == 1) { 
     $prod_qry .= " AND Hidden !='1'"; 
    } 

    if($rating != 0) { 
     $prod_qry .= " AND rating > '$rating'"; 
    } 

* 编辑 * 伊夫想通了，如下修改代码：

$prod_qry = "SELECT * FROM (SELECT id, productName, ROW_NUMBER() OVER (ORDER BY id) as row FROM products WITH(NOLOCK) WHERE shopkeeper='$this->shopkeeper'" ; 

    if($hidden == 1) { 
     $prod_qry .= " AND Hidden !='1'"; 
    } 

    if($rating != 0) { 
     $prod_qry .= " AND rating > '$rating'"; 
    }  

    $prod_qry .= ") a ";  

    if($limit > 0) { 
     $prod_qry .= " WHERE row > $start and row <= $limit"; 
    } 

Answer 1

我已经编辑上面的答案的问题。