MySQL查询以获取每日差异值 [英] MySQL query to get daily differential values
问题描述
我想制作一个 MySQL 从一个看起来像这样的表中获取每日差异值:
I want to make a MySQL to get daily differential values from a table who looks like this:
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
我做了两个子查询.第一个是获取最后一个每日值,因为我想从这些数据中计算差值:
I have made two subqueries. The first one is to get the last daily value, because I want to compute the difference values from this data:
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)
第二个是为了从第一个结果中获取差分值(我用表"名称显示):
The second one is made to get the differential values from the result of the first one (I show it with "table" name):
SELECT Date, VALUE - IFNULL(
(SELECT MAX( VALUE )
FROM table
WHERE Date < t1.table) , 0) AS diff
FROM table AS t1
ORDER BY Date
起初,我试图将第一次查询的结果保存在一个临时表中,但是 第二个查询不能使用临时表.如果我在 () 之间的第二个查询的 FROM 中使用第一个查询和别名,则服务器对表别名的投诉不存在.如何得到这样的东西:
At first, I tried to save the result of first query in a temporary table but it's not possible to use temporary tables with the second query. If I use the first query inside the FROM of second one between () with an alias, the server complaints about table alias doesn't exist. How can get a something like this:
Date | VALUE
---------------------------
"2011-01-15 00:00" | 4
"2011-01-16 00:00" | 6
推荐答案
试试这个查询 -
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
这篇关于MySQL查询以获取每日差异值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!