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] => 3 11 [1] => 4 12 [2] => 5 13 ) 14 15 [4] => Array 16 ( 17 [0] => 7 18 [1] => 8 19 [2] => 9 20 ) 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 }
