对应数据关系的交叉表

数据关系如下：
1、项目分类小计：相同的项目下，多个小分类有小计
2、分类项目小计：项目又分为国内项目和海外项目，分别对应海外小计和国内小计
3、总计 所有项目内容的总计

 

代码如下：

 

 

<?php error_reporting(E_ALL & ~E_NOTICE); $db_server = "localhost"; $db_login = "root"; $db_password = "123456"; $db_name = "q"; $link = mysql_connect($db_server, $db_login, $db_password); if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db($db_name, $link); mysql_query("set names 'gb2312'"); $str = ' '; $sql = "SELECT `num`,`localcation`,`project`,`project_category`,`code`,`item_real`,`item_budget`,`item_current_predict`,`item_current_real`,`item1`,`item2`,`item3`,`item4`, `item5`,`item6`,`tuisuan`,`tuisuan_status`,`current_status`,`comment`,`id`"; $sql .= "FROM `table` ".$str."WHERE `department` = 'ga' order by `num` asc"; $result = mysql_query($sql, $link); // 初始化统计和小计的数组 $total_p = array(); $total_c = array(); $total_m = array(); // 记录上一个项目的数据 $last_project = array(); $last_localcation =array(); // 记录显示的列表 $list = array(); // 初始化统计和小计需要统计的字段 while($row = mysql_fetch_field($result)) { // 当然你可以手工写，无所谓 if($row->type == 'real') { $total_p[$row->name] = null; $total_c[$row->name] = null; $total_m[$row->name] = null; } else { $total_p[$row->name] = ''; $total_c[$row->name] = ''; $total_m[$row->name] = ''; } // 为了显示方便 // $list[0][$row->name] = $row->name; } $total_p['localcation'] = "可控费用合计"; $total_m['project'] = "费用小计"; $total_c['project_category'] = "小计"; $curr_total_c = $total_c; $curr_total_m = $total_m; while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { if(empty($last_project)) { $last_project = $row; } else { if($last_project['project'] == $row['project']) { // 初次记录 if(!$curr_total_c['project']) { $curr_total_c['project'] = $row['project']; $curr_total_c = total_run($curr_total_c, $last_project); } // 记录小计 $curr_total_c = total_run($curr_total_c, $row); } else { // 添加到数组 if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) { $curr_total_c['num'] = count($list); $list[] = $curr_total_c; $curr_total_c = $total_c; } // 重置上一记录 $last_project = $row; } } if(empty($last_localcation)) { $last_localcation = $row; } else { if($last_localcation['localcation'] == $row['localcation']) { // 初次记录 if(!$curr_total_m['localcation']) { $curr_total_m['localcation'] = $row['localcation']; $curr_total_m['project'] = $row['localcation'].$total_m['project']; $curr_total_m = total_run($curr_total_m, $last_localcation); } // 记录小计 $curr_total_m = total_run($curr_total_m, $row); } else { // 添加到数组 if(count(array_diff_assoc($total_m, $curr_total_m)) > 0) { $curr_total_m['num'] = count($list); $list[] = $curr_total_m; $curr_total_m = $total_m; } // 重置上一记录 $last_localcation = $row; } } // 总计 $total_p = total_run($total_p, $row); // 记录内容 $list[] = $row; } // 最后判断一次是否有小计 if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) { $list[] = $curr_total_c; } if(count(array_diff_assoc($total_m, $curr_total_m)) > 0) { $list[] = $curr_total_m; } // 记录总计 $list[] = $total_p; //var_dump($list); //exit; //echo '<table>'; foreach($list as $line => $row) { $row = format_data($row); echo '<tr class="cells" οnmοuseοver="this.className=/'cells2/'" οnmοuseοut="this.className=/'cells/'">'; foreach($row as $k => $v) { if($k == 'num') $v = $v ? $v : $line; echo '<td><center><font size=2>', $v, '</font></center></td>'; } echo '</tr>'; } echo '</table>'; function format_data($row){ $row['tuisuan_status'] = ((!$row['tuisuan'])||$row['tuisuan'] <= $row['item_budget'])? '0':'1'; $row['current_status'] = (!$row['item_current_real'])||$row['item_current_real'] <= $row['item_current_predict']? '0':'1'; return $row; } function total_run($total, $row) { foreach($row as $k => $v) { if($total[$k] === null) { $total[$k] = $v; } else if(is_numeric($total[$k])) { $total[$k] += $v; } } return $total; } mysql_free_result($result); mysql_close($con); ?>