MySQL获取选择行的最大值和最小值之间的差异

Question

我想要一个查询来获取按天分组的最大值和最小值之间的差异。我的尝试不起作用:

SELECT date(`date`), 
   (max(value) - min(value)) as value,
FROM `sub_meter_data` 
where date(sub_meter_data.date) > '2012-10-01' 
  and sub_meterID in('58984','58985','58986','58987')
group by date(`date`);

每个 sub_meter 的值可能 >3000，但每天仅相差 < 10。我想要差异，即结果<10。通过上面的查询，我得到了 >3000 的结果。

下面这个查询，只选择一米，并给出正确的结果，最大值(17531)，最小值(17523)，差值(8)。

SELECT date(sub_meter_data.date) as date,
   max(value) as max_meter,
   min(value) as min_meter, 
   max(value) - min(value) as diff,
FROM `sub_meter_data`
where date(sub_meter_data.date) > '2012-10-01' 
  and sub_meterID in('57636')
  group by date(sub_meter_data.date)

但在 in 子句中添加另一个 meter，给出了一个不好的结果，max 是 17531，min 是 3021，diff 是 14510。但是我想要每个 meter 的 diff，然后加在一起。

SELECT date(sub_meter_data.date) as date,
   max(value) as max_meter,
   min(value) as min_meter, 
   max(value) - min(value) as diff,
   FROM `sub_meter_data`
where date(sub_meter_data.date) > '2012-10-01' 
  and sub_meterID in('57636', '57628')
  group by date(sub_meter_data.date)

我尝试过的另一个尝试是:

SELECT date(sub_meter_data.date) as date,
   sum(CASE WHEN sub_meterID = '57628' OR sub_meterID = '57636' THEN (max(value) -     min(value)) ELSE 0 END) as value
   FROM `sub_meter_data`
where date(sub_meter_data.date) > '2012-10-01' 

Answer 1

查询仅按天(日期)分组，但您还想按米分组，因此您需要将其添加到您的group by中:

select sub_meterID, date(`date`) as day, max(value) - min(value) as value
from `sub_meter_data`
where date(`date`) > '2012-10-01'
    and sub_meterID in ('58984','58985','58986','58987')
group by sub_meterID, date(`date`);

然后如果你想按天计算差异，你可以这样做:

select day, sum(diff) as total_diff
from (
    select sub_meterID, date(`date`) as day, max(value) - min(value) as diff
    from `sub_meter_data`
    where date(`date`) > '2012-10-01'
       and sub_meterID in ('58984','58985','58986','58987')
    group by sub_meterID, date(`date`)
    ) a
group by day

或者如果你想按米求和:

select sub_meterID, sum(diff) as total_diff
from (
    select sub_meterID, date(`date`) as day, max(value) - min(value) as diff
    from `sub_meter_data`
    where date(`date`) > '2012-10-01'
       and sub_meterID in ('58984','58985','58986','58987')
    group by sub_meterID, date(`date`)
    ) a
group by sub_meterID