我通常用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";
}
我已经用答案编辑了上面的问题。