mysql - 从电力读数mysql计算使用的千瓦时

Question

consumer_id reading_date  previous           present         KWH used

10            January       0                 30                   30
10            February     30                 60                   30
10            March        60                 70                   10
12            January       0                 30                   30
12            February     30                 30                   15

消费表

CREATE TABLE IF NOT EXISTS `consumption` (
`consumption_id` int(20) NOT NULL,
  `reading_date` date NOT NULL,
  `readings` int(20) NOT NULL,
  `kwh_used` int(20) NOT NULL,
  `meter_reader_id` int(20) NOT NULL,
  `consumer_id` int(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;

INSERT INTO `consumption` (`consumption_id`, `reading_date`, `readings`, `kwh_used`, `meter_reader_id`, `consumer_id`) VALUES
(14, '2013-07-30', 14386, 0, 1, 10),
(20, '2013-08-03', 14390, 0, 1, 10),
(29, '2013-07-30', 144, 0, 1, 12),
(31, '2013-07-31', 144, 0, 1, 12),
(34, '2013-08-03', 144, 0, 1, 12);

请检查下面我的查询，了解如何获得与上面发布的第一个表相同的输出。

SET @curRank1 = 0;
SET @curRank2 = 0;
SELECT
    D1.reading_date
    ,D1.consumer_id
    ,D1.readings AS previous
    ,D2.readings AS present
    ,(D2.readings - D1.readings) AS kwh_used
FROM 
( 
    SELECT M1.*, @curRank1 := @curRank1 + 1 AS rank
    FROM consumption M1
    LEFT JOIN consumption M2
    ON Date(M1.reading_date) = Date(M2.reading_date)
    AND M1.reading_date > M2.reading_date
    WHERE M2.reading_date IS NULL
) D2
LEFT JOIN
(
    SELECT M1.*, @curRank2 := @curRank2 + 1 AS rank
    FROM consumption M1
    LEFT JOIN consumption M2
    ON Date(M1.reading_date) = Date(M2.reading_date)
) D1
ON D2.rank = (D1.rank + 1)  
WHERE D1.reading_date IS NOT NULL

该查询的输出是这样的

reading_date    consumer_id     previous    present    kwh_used     
2013-07-30       10             14386       14390       4
2013-07-30       12             144         144         0
2013-08-03       10             14390       144         -14246
2013-08-03       12             144         144         0

正如您所看到的，查询返回了一系列读数，这是不好的，因为读数具有不同的consumer_id。请让我知道我在这里缺少什么。

谢谢。

Answer 1

您应该添加:

and M1.consumer_id = m2.consumer_id

每个子查询，以及它们之间的连接类似。

我认为有更简单的方法来获得你想要的东西(相关子查询或使用变量)。但如果您想修复查询，请将 consumer_id 放入连接条件中。