php - 查找每行的两个值之间的差异，其中 "number"列值相同

Question

month   year    customer    distributor    number   name    price   
10      2012    20          8              2406163 CHEESE   50.4
10      2012    20          8              2325141 APPLE    25.2
11      2012    20          8              2406163 CHEESE   48.1
11      2012    20          8              2325141 APPLE    20.2
12      2012    20          8              2325141 APPLE    23.2
12      2012    20          8              2406163 CHEESE   46.4

我试图比较“数字”具有相同值的两个月，我想找出价格之间的差异(如果有)。

使用上面的数据，我想比较第 11 个月和第 12 个月(或第 10 个月和第 11 个月，具体取决于用户选择的月份)。因此，在第 11 个月和第 12 个月，奶酪的价差为 - 1.7 美元，苹果的价差为 - 3 美元。这就是我想要做的，除了它应该对两个月之间表中找到的所有行执行此操作，其中“number”相同。

这是我当前的代码...它当前按项目名称而不是项目编号进行比较。然而，无论出于什么原因，它只发现 200 多个项目中的 3 个项目存在差异，这很奇怪。

感谢您的帮助。

<?PHP
$from_date = $from_month;
$to_date = $to_month;
$query = " 
   select * from ogi
   where month BETWEEN '" . $from_date . "' AND  '" . $to_date . "' GROUP BY number HAVING count(*) > 1 ;
"; 
try 
{ 
    // These two statements run the query against your database table. 
    $stmt = $db->prepare($query); 
    $stmt->execute(); 
} 
catch(PDOException $ex) 
{ 
    // Note: On a production website, you should not output $ex->getMessage(). 
    // It may provide an attacker with helpful information about your code.  
    die("Failed to run query: " . $ex->getMessage()); 
} 

// Finally, we can retrieve all of the found rows into an array using fetchAll 
$rows = $stmt->fetchAll(); 

//Create a variable to hold the "previous month" values
$previousname = '';
$previousprice = '';

//Check each record from your query
foreach($rows as $row) {

//If not first time around && same item name as previous
if($previousname != '' && $previousname == $row['name']) {

  //subtraction calculation here

  if ($row['price'] <= $previousprice) {
$difference = "(Price Lowered) Price difference of $";
$result = $previousprice - $row['price'];
$percent = round(100.0*($previousprice/$row['price']-1));

   ?>
            <tr>
                <td><?php echo "" . $row['name'] . ""?></td>
                <td><?php echo $difference; ?><?php echo $result ?>        </td>
                <td><?php echo $percent; ?> %</td>

            </tr>
            <?
} elseif ($row['price'] > $previousprice) {
$result = $row['price'] - $previousprice;
$percent = round(100.0*($previousprice/$row['price']-1));
$addition = "(Price Higher) Price difference of $";

 ?>
            <tr>
                <td><?php echo "" . $row['name'] . ""?></td>
                <td><?php echo $addition; ?><?php echo $result ?>    </td>
                <td><?php echo $percent; ?>%</td>

            </tr>
            <?
} 
   }
   else {
   ?>
   <!-- <tr>
                <td><?php echo "" . $row['name'] . ""?></td>
                <td></td>
                <td></td>

            </tr> -->
            <?

}

//Assign the "previous month" value
$previousname = $row['name'];
$previousprice = $row['price'];

}


?>
<?
}
else {
?>
<form action="" method="post">
Choose Months to Compare:<br>
Customer: <input type="text" name="customer"><br>
Month 1: <input type="text" name="from_month"><br>
Month 2: <input type="text" name="to_month"><br>
<input type="submit" value="Compare">
</form>
<?
}
?>

Answer 1

像这样的东西怎么样:

SELECT a.number, a.name, (a.price - b.price) as pdiff
FROM table a, table b
WHERE a.number = b.number 
    AND a.month = montha 
    AND b.month = monthb;

如果我没有犯任何错误，此查询应该为您提供所需的信息:

(where montha = 10 and monthb = 11 for example)

希望这有帮助。