MySQL查询获得每日差异值

Question

我想使一个MySQL从表中谁看起来像这样每天都得到差值：

Date    | VALUE 
-------------------------------- 
"2011-01-14 19:30" | 5 
"2011-01-15 13:30" | 6 
"2011-01-15 23:50" | 9 
"2011-01-16 9:30" | 10 
"2011-01-16 18:30" | 15 

我做了两个子查询。第一个是拿到最后的日常价值，因为我想从这个数据计算差值：

SELECT r.Date, r.VALUE 
    FROM table AS r 
    JOIN (
    SELECT DISTINCT max(t.Date) AS Date 
    FROM table AS t 
    WHERE t.Date < CURDATE() 
    GROUP BY DATE(t.Date) 
    ) AS x USING (Date) 

第二个是由获得从第一个结果的差值（我秀它与“表”的名称）：

SELECT Date, VALUE - IFNULL( 
    (SELECT MAX(VALUE) 
    FROM table 
    WHERE Date < t1.table) , 0) AS diff 
    FROM table AS t1 
    ORDER BY Date 

起初，我想第一个查询的结果保存在临时表，但it's not possible to use temporary tables with the second query。如果我在（）与别名之间的第二个FROM内使用第一个查询，那么关于表别名的服务器投诉不存在。如何获得这样的事情：

Date    | VALUE 
--------------------------- 
"2011-01-15 00:00" | 4 
"2011-01-16 00:00" | 6 

Answer 1

尝试此查询 -

SELECT 
    t1.dt AS date, 
    t1.value - t2.value AS value 
FROM 
    (SELECT DATE(date) dt, MAX(value) value FROM table GROUP BY dt) t1 
JOIN 
    (SELECT DATE(date) dt, MAX(value) value FROM table GROUP BY dt) t2 
    ON t1.dt = t2.dt + INTERVAL 1 DAY