有N个数据表 例如cdr_20090901 ,cdr_20090902,cdr_20090903,cdr_20090904,cdr_20090905。cdr_20091212.
求这些09年9月1日到09年12月12日表中某一字段money的 总值。如何写代码哈 不需要union语句,,太多了。 $query=mysql_query("SELECT sum( money) as pnum from (SELECT moneyFROM cdr_20090901 callerid like '%$keyword%' UNION
SELECT money FROM cdr_20090902 WHERE callerid like '%$keyword%'UNION
SELECT money FROM cdr_20090903 WHERE callerid like '%$keyword%'UNION .......)A");
$out=mysql_fetch_array($query);
echo "该用户money总值:$out[pnum]";
用union的话太复杂了,N个表呢。
有循环语句吗??请求高手指教!!
求这些09年9月1日到09年12月12日表中某一字段money的 总值。如何写代码哈 不需要union语句,,太多了。 $query=mysql_query("SELECT sum( money) as pnum from (SELECT moneyFROM cdr_20090901 callerid like '%$keyword%' UNION
SELECT money FROM cdr_20090902 WHERE callerid like '%$keyword%'UNION
SELECT money FROM cdr_20090903 WHERE callerid like '%$keyword%'UNION .......)A");
$out=mysql_fetch_array($query);
echo "该用户money总值:$out[pnum]";
用union的话太复杂了,N个表呢。
有循环语句吗??请求高手指教!!
用一个for循环取出每一张表中money字段的值存入一个数组中然后再用array_sum求和 出来的结果就是你要的
$sql = "SHOW TABLES";
$result = mysql_query($sql);
$total_money = 0;
while($rs = mysql_fetch_array($result))
{
$sql2 = "SELECT SUM(money) FROM ".$rs[0]."";
$result2 = mysql_query($sql2);
$rs2 = mysql_fetch_array($result2);
mysql_free_result($result2);
$total_money += $rs2[0];
}
mysql_free_result($result);
echo $total_money;
/*下面是遍历日期段的天数*/
function day($date1,$date2)
{
$time1 = $date1 != '' ? strtotime($date1) : time();
$time2 = $date2 != '' ? strtotime($date2) : time();
return abs(($time2 - $time1)/(3600*24));
}
function getMonthDays($year1,$month)
{
$next = $month + 1;
$year2 = $year1;
if($next > 12)
{
$next = 1;
$year2++;
}
$currentMonth = $year1.'-'.$month.'-1';
$nextMonth = $year2.'-'.$next.'-1';
$days = day($nextMonth,$currentMonth);
return $days;
}
function foreachDay($date1,$date2)
{
list($year1,$month1,$day1) = split('[/.-]',$date1);
$totalDays = day($date2,$date1);
for ($i = 0; $i < $totalDays; $i++)
{
if($day1<10){
$day1='0'.$day1;
}
$table[]=$year1.$month1.$day1;//天数数组
$currentMonthDays = getMonthDays($year1,$month1);
$day1 = intval($day1,10) + 1;
if ($day1 > $currentMonthDays) {
$day1 = 1;
$month1++;
}
if ($month1 > 12) {
$month1 = 1;
$year1++;
}
}
return $table;
}
$arr=array();//天数数组
$sum=array();//money数组
$arr=foreachDay('2009-09-01','2009-12-13');
for($i=0;$i<count($arr);$i++){
$sql="select sum(money) from cdr_".$arr[$i];
$rs=mysql_query($sql);
$rw=mysql_fetch_row($rs);
$sum[]=$rw[0];
}
echo array_sum($sum);