PHP Mysql 根据累计值按日期分组

Question

我有一个包含 ID、value1、value2、value3 和 tstamp(TIMESTAMP 格式)的表，我尝试按每天分组并查找每天的总计，但是所有值都是累积的，这会带来问题，因为 sum(value1) 没有给出正确的输出，这是我的代码:

$sql = "select date(tstamp), sum(".$column.") from mash group by date(tstamp) order by tstamp asc limit 10";
$result = mysql_query($sql);
$previous = 0;
$firstRun = true;
while($row = mysql_fetch_array($result))
{
  $difference = $row[1] - $previous;
  if (!$firstRun)
  {
     $strXML .= "<set name='".$row[0]."' value='".$difference."' color='AFD8F8' />";
   }
  $previous = $row[1];
  $firstRun = false;
}

任何人都可以发现这段代码中的问题吗？它没有错误，只是给出了错误的答案。

编辑:

为了消除任何混淆，这是 SQL:

 --------------------------------------------------------

--
-- Table structure for table `mash`
--

CREATE TABLE IF NOT EXISTS `mash` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `steam` int(25) NOT NULL,
  `bore_water` int(25) NOT NULL,
  `boiler1oil` int(25) NOT NULL,
  `boiler2oil` int(25) NOT NULL,
  `tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5362 ;

--
-- Dumping data for table `mash`
--

INSERT INTO `mash` (`id`, `steam`, `bore_water`, `boiler1oil`, `boiler2oil`, `tstamp`) VALUES
(2, 436, 73, 15, 1, '2010-11-25 12:28:03'),
(3, 495, 74, 36, 1, '2010-11-25 12:38:04'),
(4, 553, 76, 58, 1, '2010-11-25 12:48:09'),
(5, 565, 77, 74, 1, '2010-11-25 12:58:05'),
(6, 584, 79, 78, 1, '2010-11-25 13:08:05'),
(7, 630, 82, 100, 1, '2010-11-25 13:18:11'),
(8, 686, 86, 130, 1, '2010-11-25 13:28:07'),
(9, 740, 89, 151, 1, '2010-11-25 13:38:07'),
(10, 780, 93, 173, 1, '2010-11-25 13:48:13'),
(11, 883, 100, 218, 1, '2010-11-25 14:08:10');

Answer 1

假设我正确地阅读了你的表格，你正在寻找“value1”(或value2等)的最后一个条目，它实际上是总和(根据你提到的累积值来判断)

+----+------+---------------------+
| id | val  | tstamp              |
+----+------+---------------------+
|  6 |    1 | 2010-01-02 01:00:00 |
|  7 |    4 | 2010-01-02 02:00:00 |
|  8 |    6 | 2010-01-02 03:00:00 |
|  9 |   15 | 2010-01-02 04:00:00 |
| 10 |   20 | 2010-01-02 05:00:00 | <-- this value
| 11 |    1 | 2010-01-03 01:00:00 |
| 12 |    4 | 2010-01-03 02:00:00 |
| 13 |    6 | 2010-01-03 03:00:00 |
| 14 |   15 | 2010-01-03 04:00:00 |
| 15 |   20 | 2010-01-03 05:00:00 | <- this value
|  1 |    2 | 2010-02-01 01:00:00 |
|  2 |    8 | 2010-02-01 02:00:00 |
|  3 |   16 | 2010-02-01 03:00:00 |
|  4 |   32 | 2010-02-01 04:00:00 |
|  5 |   64 | 2010-02-01 05:00:00 | <- this value
+----+------+---------------------+

那么您应该能够使用 MAX&DATE:

SELECT    DATE(tstamp), MAX(val)
FROM      mash
GROUP     DATE(tstamp)
ORDER BY  tstamp ASC;

这将产生:

+--------------+----------+
| DATE(tstamp) | MAX(val) |
+--------------+----------+
| 2010-01-02   |       20 |
| 2010-01-03   |       20 |
| 2010-02-01   |       64 |
+--------------+----------+