mysql sum 在流程图中输出

Question

我正在尝试输出两个表条目与 flot/jquery 图表上的日期的运行总计。

到目前为止，代码可以正常工作，可以在给定日期输出两个值的折线图，但我试图在同一图表上显示每列的运行总计。不确定是否可以使用 SUM(value) 使用单个 mysql 查询来实现此目的？ - 只是无法让它发挥作用。

<?php

$query=("SELECT hours, credits, entryDate FROM entry WHERE uid='1'");

$result = mysql_query($query);        

while($row = mysql_fetch_assoc($result))
{
    $credits[] = array ( strtotime ($row['entryDate'])*1000,($row['credits']));
    $hours[] = array ( strtotime ($row['entryDate'])*1000,($row['hours']));
} ?>

</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>

Answer 1

最简单的方法就是在 php 中保存一个运行总计，并将每个日期也列出来:-

<?php

$query=("SELECT hours, credits, entryDate FROM entry WHERE uid='1' ORDER BY entryDate");

$result = mysql_query($query);        

while($row = mysql_fetch_assoc($result))
{
    $runningtotalcredits += $row['credits'];
    $runningtotalhours += $row['hours'];
    $credits[] = array ( strtotime ($row['entryDate'])*1000,$row['credits']);
    $creditssofar[] = array ( strtotime ($row['entryDate'])*1000,$runningtotalcredits);
    $hours[] = array ( strtotime ($row['entryDate'])*1000,$row['hours']);
    $hourssofar[] = array ( strtotime ($row['entryDate'])*1000,$runningtotalhours);
} ?>

</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>
var creditssofar = <?php echo json_encode($creditssofar);?>
var hourssofar = <?php echo json_encode($hourssofar);?>

也应该可以使用相关子查询在 SQL 中完成此操作，但效率可能不太好:-

<?php

$query=("SELECT hours, 
                credits, 
                entryDate, 
                (SELECT SUM(hours) AS hourssofar FROM entry b WHERE uid='1' AND b.entryDate <= a.entryDate),
                (SELECT SUM(credits) AS creditssofar FROM entry c WHERE uid='1' AND c.entryDate <= a.entryDate)
        FROM entry a
        WHERE uid='1' 
        ORDER BY entryDate");

$result = mysql_query($query);        

while($row = mysql_fetch_assoc($result))
{
    $credits[] = array ( strtotime ($row['entryDate'])*1000,$row['credits']);
    $creditssofar[] = array ( strtotime ($row['entryDate'])*1000,$row['creditssofar']);
    $hours[] = array ( strtotime ($row['entryDate'])*1000,$row['hours']);
    $hourssofar[] = array ( strtotime ($row['entryDate'])*1000,$row['hourssofar']);
} ?>

</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>
var creditssofar = <?php echo json_encode($creditssofar);?>
var hourssofar = <?php echo json_encode($hourssofar);?>

(未测试)