用mysql查询表，今天用电量减去昨日用电量，然后对比昨日增加或者降低了百分之多少

Question

Answer 1

用表的自连接，连接条件是今天减一天等于昨天的数据，SQL语句如下：

SELECT 
DATE(A.create_time) AS 'yesterday',
DATE(B.create_time) AS 'today',
A.electric_number AS 'yesterday_electric_number',
B.electric_number AS 'today_electric_number',
(B.electric_number-A.electric_number) AS variation,
((B.electric_number-A.electric_number)/A.electric_number)*100 AS ratio
FROM electric_record AS A INNER JOIN electric_record AS B
ON DATE(A.create_time)=DATE(B.create_time-INTERVAL 1 DAY)

执行结果如图：