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。请让我知道我在这里想念的东西。
谢谢。
最佳答案
您应该添加：
and M1.consumer_id = m2.consumer_id
到每个子查询，以及它们之间的
join。我认为有更简单的方法来获取所需的内容（相关子查询或使用变量）。但是，如果要修复查询，请在连接条件中放入
consumer_id。关于mysql - 从电读数MySQL计算使用的千瓦时，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29064812/