php - 在单个查询中两次选择同一个表

Question

我的 table :

id | views | date
1  | 100   | 2017-03-09
2  | 150   | 2017-03-10
3  | 300   | 2017-03-11
4  | 350   | 2017-03-12

我需要像这样计算几天之间的访问次数差异

2017-03-12-->Visitors:350
2017-03-11-->Visitors:300
Difference between days:50

2017-03-11-->Visitors:300
2017-03-10-->Visitors:150
Difference between days:150

2017-03-10-->Visitors:150
2017-03-09-->Visitors:100
Difference between days:50

and so on...

我设法得到了类似的结果，但并不完全是我想要的

$sql = "SELECT * FROM `table` ORDER BY `table`.`id` DESC";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result))
{

    $t = $row['views'];  
    $dat = $row['date'];  


    $sql1 = "SELECT * FROM `table` ORDER BY `table`.`id` DESC LIMIT 1, 99";
    $result1 = mysql_query($sql1) or die(mysql_error());
    while($row1 = mysql_fetch_array($result1))
    {

        $y = $row1['views'];  
        $dat1 = $row1['date'];  

        $d = $t-$y;

        echo "{$dat}-->Visitors:{$t}";
        echo "<br/>";
        echo "{$dat1}-->Visitors:{$y}";
        echo "<br/>";
        echo "Difference between days:{$d}";
        echo "<br/><br/><br/>";                     
    }                       
}

所以我想我需要用一个查询选择同一张表两次。

Answer 1

这里不需要 SQL 技巧。

您正在按日期顺序显示行。只需将最后一行的计数保存在一个变量中，在 php 中进行减法，就可以得到不同的结果。

删除嵌套循环。您只需要一个循环。

$last_views = null;

while($row = mysql_fetch_array($result))
{
    $views = $row['views'];  
    $dat   = $row1['date'];  

    if( $last_views === null ) 
         $delta_views = "";
    else
         $delta_views = $last_views - $views;

    $last_views = $views;

    echo "{$dat}-->Visitors:{$views}";
    echo "<br/>";
    echo "Difference between days:{$delta_views}";
    echo "<br/>";                     
}