合併資料表平均值

edited 五月 2014 in 進階PHP討論
請益各位高手大大,

有3個相同的column的表格,但是內容不同。表格如下:

a2 a4 a6
pid session a2a a2b pid session a4a a4b pid session a6a a6b
============ ============ ============
1 test 1 0 1 test 0 0 1 test 1 0
2 try 0 1 2 try 1 1 2 try 0 1


合併3個表格成為一個的程式如下:


SELECT a2.session,a2.a2a,a2.a2b,a4.a4a,a4.a4b,a6.a6a,a6.a6b
FROM a2
JOIN a4 ON a4.session = a2.session
JOIN a6 ON a6.session = a4.session


得到的結果就是:

session a2a a2b a4a a4b a6a a6b
================================
test 1 0 0 0 1 0
try 0 1 1 1 0 1


我想請益的問題,我想要做每個session的平均值該如何寫?

session a2a a2b a4a a4b a6a a6b avg
====================================
test 1 0 0 0 1 0 ?
try 0 1 1 1 0 1 ?

我寫的語法是:

SELECT \"a2.session,a2.a2a,a2.a2b,a4.a4a,a4.a4b,a6.a6a,a6.a6b
AND AVG(a2.a2a+a2.a2b+a4.a4a+a4.a4b+a6.a6a+a6.a6b)/6
FROM a2
JOIN a4 ON a4.session = a2.session
JOIN a6 ON a6.session = a4.session
WHERE `session` LIKE '\".$_SESSION[\"loginMember\"].\"'\";

<table width=\"1200\" border=\"1\">
<tr>
<td align='center'><div align=\"center\">session</div></td>
<td align='center'><div align=\"center\">a2a</div></td>
<td align='center'><div align=\"center\">a2b</div></td>
<td align='center'><div align=\"center\">a4a</div></td>
<td align='center'><div align=\"center\">a4b</div></td>
<td align='center'><div align=\"center\">a6a</div></td>
<td align='center'><div align=\"center\">a6b</div></td>
</tr>
<?php
$bgcolor = \"\";
$content = \"\";
mysql_data_seek($result, 0);
while($record=mysql_fetch_array($result)){
echo \"<tr>\";
echo \"<td>\".$record['session'].\"</td>\";
echo \"<td>\".$record['a2a'].\"</td>\";
echo \"<td>\".$record['a2b'].\"</td>\";
echo \"<td>\".$record['a4a'].\"</td>\";
echo \"<td>\".$record['a4b'].\"</td>\";
echo \"<td>\".$record['a6a'].\"</td>\";
echo \"<td>\".$record['a6b'].\"</td>\";
echo \"</tr>\";
}

?>

</table>


請問如何讓avg出現?

謝謝各位大大幫忙
Sign In or Register to comment.