MySQL更新字段基于三分之一分组时另一个字段的最小值 [英] MySQL update field based on min value of another field when grouped by a third

问题描述

我已经阅读了一些有关选择最小值，分组等的主题，但似乎无法创建有效的查询来解决此问题.如果有重复，请原谅.

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

基本上希望有一个更新查询，当它具有按日期分组的最低值时，将其值填充到TAG字段中.在此示例中，其ID为2和6.

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.

更新表设置标签= 1(选择最小值?)

update table set tag = 1 (select min?)

推荐答案

您可以通过计算min()并使用join进行过滤来做到这一点:

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;

这篇关于MySQL更新字段基于三分之一分组时另一个字段的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！