基于分组数据的特定mysql更新 [英] specific mysql update based on group by data

问题描述

这个让我难过.

我有2张桌子，

METERS
id | startTime

READINGS
id | meter_id | readingTime

我想做的是将meters.startTime更新为1个SQL查询中最低的匹配readings.readingTime.

What I want to do is update the meters.startTime to the lowest matching readings.readingTime in 1 sql query.

我该怎么做?

推荐答案

像这样:

UPDATE Meters m
INNER JOIN
(
   SELECT meter_id, MIN(reading_time) lowesttime
   FROM readings 
   GROUP BY meter_id
) r ON m.id = r.meter_id
SET m.starttime = r.lowesttime;

这篇关于基于分组数据的特定mysql更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！