MSSQL limit 修正

六月峰 发表于 2012-11-2 13:31:39

CI2.0版本的limit在MSSQL或Sqlsrv下不起作用，看了CI 3.0 在github上develop分支对limit的修正也是存在问题跑不起来的，不知为何错误的mssql语句也会push上去，这里分享一下我修改后的函数，在CI 3 上测试通过

覆盖database/drivers/mssql/mssql_driver.php 以及 database/drivers/sqlsrv/sqlsrv _driver.php 里的_limit函数。

protected function _limit($sql, $limit, $offset) {
if ( ! $offset)
{
return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
}

// As of SQL Server 2012 (11.0.*) OFFSET is supported
if (version_compare($this->version(), '11', '>='))
{
return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY';
}

// As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
if (version_compare($this->version(), '9', '>='))
{
$orderby = 'ORDER BY ' . (empty($this->qb_orderby) ? 'getdate()' : implode(', ', $this->qb_orderby));
$sql = empty($this->qb_orderby) ? $sql : trim(substr($sql, 0, strrpos($sql, $orderby)));

return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
."\n) AS ".$this->escape_identifiers('CI_subquery')
."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '. ($offset + $limit);
}

// TODO: Lower than 9.0 version (SQL Server 2000) need you to write
return $sql;
}

CurveSoft 发表于 2012-11-27 03:21:03

您这个方法在CI 2.1.3下没法用，qb_orderby这个属性不存在。。

CurveSoft 发表于 2012-11-27 14:39:05

本帖最后由 CurveSoft 于 2012-11-27 14:40 编辑

哈哈，感谢楼主，我稍微修改了一下，2.1.3可以用了！太感谢了。

以下是我修改的代码，修改的地方主要是把$this->qb_orderby改为$this->ar_orderby，$this->escape_identifiers改为$this->_escape_identifiers，就O了，哈哈！

function _limit($sql, $limit, $offset) {
   // $i = $limit + $offset;

   // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
   if (! $offset) {
         return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql );
   }

   // As of SQL Server 2012 (11.0.*) OFFSET is supported
   if (version_compare ( $this->version (), '11', '>=' )) {
         return $sql . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
   }

   // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
   if (version_compare ( $this->version (), '9', '>=' )) {
         $orderby = 'ORDER BY ' . (empty ( $this->ar_orderby ) ? 'getdate()' : implode ( ', ', $this->ar_orderby ));
         $sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, strrpos ( $sql, $orderby ) ) );

         return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
   }

   // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
   return $sql;
}

CurveSoft 发表于 2012-11-27 16:10:26

本帖最后由 CurveSoft 于 2012-11-27 16:11 编辑

又修改了一下，楼主原来代码中
$sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, strrpos ( $sql, $orderby ) ) );
这段似乎本意是$sql中原来有ORDER BY的话就移除，因为MSSQL 2005不允许子查询中有ORDER BY，但是如果$sql里面原来没有ORDER BY子句的话，这样做整个SQL就会被清空了
于是我改了一下，如下：

function _limit($sql, $limit, $offset) {
   // $i = $limit + $offset;

   // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
   if (! $offset) {
         return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql );
   }

   // As of SQL Server 2012 (11.0.*) OFFSET is supported
   if (version_compare ( $this->version (), '11', '>=' )) {
         return $sql . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
   }

   // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
   if (version_compare ( $this->version (), '9', '>=' )) {
         $orderby = 'ORDER BY ' . (empty ( $this->ar_orderby ) ? 'getdate()' : implode ( ', ', $this->ar_orderby ));
         $p = strrpos ( $sql, 'order by' );
         if ($p) {
            $sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, $p ) );
         }

         return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
   }

   // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
   return $sql;
}

不知道这样对不对，至少在我的应用里面不会有问题，因为我的查询SQL里面不会在第一步SQL里写ORDER BY的。

CurveSoft 发表于 2012-11-27 16:47:11

好吧，最终版本来了

function _limit($sql, $limit, $offset) {
   // $i = $limit + $offset;

   // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
   $orderby = ' ORDER BY getdate()';
   if (! empty ( $this->ar_orderby )) {
         $p = strrpos ( $sql, 'order by' );
         if ($p) {
            $sql = trim ( substr ( $sql, 0, $p ) );
         }
         $orderby = ' ORDER BY ' . implode ( ', ', $this->ar_orderby );
   }

   if (! $offset) {
         return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql . $orderby );
   }

   // As of SQL Server 2012 (11.0.*) OFFSET is supported
   if (version_compare ( $this->version (), '11', '>=' )) {
         return $sql . $orderby . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
   }

   // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
   if (version_compare ( $this->version (), '9', '>=' )) {

         return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
   }

   // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
   return $sql;
}

zeek 发表于 2014-1-22 15:07:28

本帖最后由 zeek 于 2014-1-22 15:08 编辑

修改成这样吧，可以完美运行
function _limit($sql, $limit, $offset) {
   if (count($this->ar_orderby) > 0)
   {
         $OrderBy= "ORDER BY ";
         $OrderBy .= implode(', ', $this->ar_orderby);

         if ($this->ar_order !== FALSE)
         {
            $OrderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC';
         }
   }

   $sql = preg_replace('/(\\'. $OrderBy .'\n?)/i','', $sql);
   $sql = preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 row_number() OVER ('.$OrderBy.') AS rownum, ', $sql);

   $NewSQL = "SELECT * \nFROM (\n" . $sql . ") AS A \nWHERE A.rownum BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")";

   return $NewSQL;
}

页: [1]

CodeIgniter 中国开发者社区's Archiver

MSSQL limit 修正