2014年11月29日21:01:01

场景：有的时候查询数据库的select in 语句中会有非常多不连续的数值，会很影响查询效率

方法：将select in 查询转换成多个select between and 语句

代码：核心代码(id要排过顺序)

1     public function group() 2     { 3         // $a = array(1,2,3,5,7,8,9); 4         $a = array(1,3,4,5,7,8,9); 5         $len = count($a); 6         $cur = 0; //当前遍历元素的下标 7         $pre = $a[0]; //前一个元素的值 8  9         $new = array('0' => array($a[0]));10         for ($i = 1; $i < $len; $i++) {11             if (($a[$i] - $pre) == 1 ) {12                 $new[$cur][] = $a[$i];13             } else {14                 $cur = $i;15                 $new[$cur][] = $a[$i];16             }17             $pre = $a[$i];18         }19 20         print_r($new);21     }

//结果

1 Array 2 ( 3     [0] => Array 4         ( 5             [0] => 1 6         ) 7  8     [1] => Array 9         (10             [0] => 311             [1] => 412             [2] => 513         )14 15     [4] => Array16         (17             [0] => 718             [1] => 819             [2] => 920         )21 22 )

 完整代码:

1 //select in 2     //arrData 整数数组，最好是整数 3     public function select_in($key, $arrData, $fields='') 4     { 5         $fields = $fields ? $fields : '*'; 6         sort($arrData); 7         $len = count($arrData); 8         $cur = 0; 9         $pre = $arrData[0];10 11         $new = array('0' => array($arrData[0]));12         for ($i = 1; $i < $len; $i++) {13             if (($arrData[$i] - $pre) == 1 ) {14                 $new[$cur][] = $arrData[$i];15             } else {16                 $cur = $i;17                 $new[$cur][] = $arrData[$i];18             }19             $pre = $arrData[$i];20         }21 22         $arrSql = array();23         foreach ($new as $v) {24             $len = count($v) - 1;25             if ($len) {26                 $s = $v[0];27                 $e = end($v);28                 $sql = "(select $fields from {
   $this->_dt} where $key between $s and $e)";29             } else {30                 $s = $v[0];31                 $sql = "(select $fields from {
   $this->_dt} where $key = $s)";32             }33 34             $arrSql[] = $sql;35         }36         37         $strUnion = implode(' UNION ALL ', $arrSql);38         $res = $this->query($strUnion);39         return $this->rstoarray($res);40     }