对应数据关系的交叉表

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

 

代码如下：

 

 

<?phperror_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" onmouseover="this.className=/'cells2/'" onmouseout="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);?>