2014年11月29日21:01:01
场景：有的时候查询数据库的select in 语句中会有非常多不连续的数值，会很影响查询效率
方法：将select in 查询转换成多个select between and 语句
代码：核心代码(id要排过顺序)
public function group()
{
// $a = array(1,2,3,5,7,8,9);
$a = array(1,3,4,5,7,8,9);
$len = count($a);
$cur = 0; //当前遍历元素的下标
$pre = $a[0]; //前一个元素的值 $new = array('0' => array($a[0]));
for ($i = 1; $i < $len; $i++) {
if (($a[$i] - $pre) == 1 ) {
$new[$cur][] = $a[$i];
} else {
$cur = $i;
$new[$cur][] = $a[$i];
}
$pre = $a[$i];
} print_r($new);
}
//结果
Array
(
[0] => Array
(
[0] => 1
) [1] => Array
(
[0] => 3
[1] => 4
[2] => 5
) [4] => Array
(
[0] => 7
[1] => 8
[2] => 9
) )
完整代码:
//select in
//arrData 整数数组，最好是整数
public function select_in($key, $arrData, $fields='')
{
$fields = $fields ? $fields : '*';
sort($arrData);
$len = count($arrData);
$cur = 0;
$pre = $arrData[0]; $new = array('0' => array($arrData[0]));
for ($i = 1; $i < $len; $i++) {
if (($arrData[$i] - $pre) == 1 ) {
$new[$cur][] = $arrData[$i];
} else {
$cur = $i;
$new[$cur][] = $arrData[$i];
}
$pre = $arrData[$i];
} $arrSql = array();
foreach ($new as $v) {
$len = count($v) - 1;
if ($len) {
$s = $v[0];
$e = end($v);
$sql = "(select $fields from {$this->_dt} where $key between $s and $e)";
} else {
$s = $v[0];
$sql = "(select $fields from {$this->_dt} where $key = $s)";
} $arrSql[] = $sql;
} $strUnion = implode(' UNION ALL ', $arrSql);
$res = $this->query($strUnion);
return $this->rstoarray($res);
}