select tt.id,tt.a1,ff.a2 from (select id,sum(fd1) as a1 from table2 group by id) as tt LEFT JOIN (select id,count(fd2) as a2 from table3 group by id) as ff on tt.id=ff.id
gzh_seagull(不可) 老大,此句在ms sql server 中没问题,通过。 但是mysql中过不了。 还有,table1中的id都要,也就是说table1中的id唯一,然后后面显示另外两个表中id对应的记录的统计数据,一共三个表,一个也不能缺。
<?php $conn=mysql_connect("localhost","root",""); @mysql_select_db("test",$conn); $query="DROP TABLE IF EXISTS tmp"; $result=mysql_query($query); $query="create temporary table tmp SELECT table1.id, sum( table2.fd1 ) as fd1 FROM table1 LEFT JOIN table2 ON table2.id = table1.id group by table1.id"; $result=mysql_query($query); $query="select tmp.id, tmp.fd1, count( table3.fd2 ) as fd2 FROM tmp LEFT JOIN table3 ON table3.id = tmp.id GROUP BY tmp.id"; $result=mysql_query($query); while($row=mysql_fetch_array($result)) { echo $row[id]."-".$row[fd1]."-".$row[fd2]."<br>"; } @mysql_close(); ?>
但是mysql中过不了。
还有,table1中的id都要,也就是说table1中的id唯一,然后后面显示另外两个表中id对应的记录的统计数据,一共三个表,一个也不能缺。
$conn=mysql_connect("localhost","root","");
@mysql_select_db("test",$conn);
$query="DROP TABLE IF EXISTS tmp";
$result=mysql_query($query);
$query="create temporary table tmp SELECT table1.id, sum( table2.fd1 )
as fd1
FROM table1
LEFT JOIN table2 ON table2.id = table1.id
group by table1.id";
$result=mysql_query($query);
$query="select tmp.id, tmp.fd1, count( table3.fd2 ) as fd2 FROM tmp LEFT JOIN table3 ON table3.id = tmp.id GROUP BY tmp.id";
$result=mysql_query($query);
while($row=mysql_fetch_array($result))
{
echo $row[id]."-".$row[fd1]."-".$row[fd2]."<br>";
}
@mysql_close();
?>
能不能再想想,帮忙帮忙。
谢谢