mysql 设置字段最小值,MySQL更新字段基于三分之一分组时另一个字段的最小值

I have read several threads on selecting for min, grouping etc but can't seem to create an efficient query to solve this. Please forgive if duplicate.

I have a table like:

ID Date Value Tag

1 1/1/13 500 NULL

2 1/1/13 10 NULL

3 1/1/13 12 NULL

4 1/2/13 99 NULL

5 1/2/13 136 NULL

6 1/2/13 17 NULL

Basically want an update query that populates the TAG field with a value of 1 when it has the lowest value grouped by date. In this example that would be IDs 2 and 6.

update table set tag = 1 (select min?)

解决方案

You can do this by calculating the min() and using a join to do the filtering:

update t join

(select date, min(value) as minvalue

from t

group by date

) tmin

on t.date = tmin.date and t.value = tmin.minvalue

set tag = 1;