时间戳

首页 > 解决方案 > SQL：从最大值更新最小值

sql - SQL：从最大值更新最小值

问题描述

我正在努力通过 VB 和 OleDB 更新 myTable 中的列。

我的表：

myGroup  Value1  Value2
A          20       5
B          15       3
A          19       4
A          20       6
C          10       2
B          14       4
C          11       7

我想用 value2 更新列 value3，其中当 value1 在组中最高时 value2 最低。

我有这个：

SELECT myGroup ,MAX(value1), MIN(value2)
FROM myTable
GROUP BY myGroup

我明白了：

myGroup  Value1  Value2
A          20       4
B          15       3
C          11       2

但我需要这个：

myGroup  Value1  Value2
A          20       5
B          15       3
C          11       7

我需要类似“UPDATE mytable SET Value3 = MIN(value2) WHERE MAX(value1) GROUP BY myGroup”

myTabel 应该是这样的：

myGroup  Value1  Value2  Value3
A          20       5       5
B          15       3       3
A          19       4       5
A          20       6       5
C          10       2       7
B          14       4       3
C          11       7       7

请提供任何帮助。

标签： sqlms-accessoledb

解决方案

您可以使用如下查询：

SELECT t1.myGroup, t1.value1, t2.value2 as value3
  FROM myTable t1
  LEFT JOIN
(
SELECT myGroup, Value1, MIN(VALUE2) AS value2
  FROM myTable
 WHERE (myGroup, Value1) in
  (
   SELECT myGroup , MAX(value1)
     FROM myTable
    GROUP BY myGroup
  ) 
 GROUP BY myGroup, Value1) t2 
    ON ( t1.myGroup = t2.myGroup );

myGroup Value1  Value3
------- ------  ------
    A     20      5
    B     15      3
    A     19      5
    A     20      5
    C     10      7
    B     14      3
    C     11      7

选择的 Rextester 演示

对于上面的 SELECT 语句，UPDATE 语句可能是：

UPDATE myTable t3
   SET t3.Value2 =
            ( SELECT q.value3 FROM 
              (
                SELECT t1.myGroup, t1.value1, t1.value2, t2.value2 as value3
                  FROM myTable t1
                  LEFT JOIN
                (
                SELECT myGroup, Value1, MIN(VALUE2) AS value2
                  FROM myTable
                 WHERE (myGroup, Value1) in
                  (
                   SELECT myGroup , MAX(value1)
                     FROM myTable
                    GROUP BY myGroup
                  ) 
                 GROUP BY myGroup, Value1) t2 
                    ON ( t1.myGroup = t2.myGroup )
               ) q
               WHERE q.myGroup = t3.myGroup 
                 AND q.value1 = t3.value1
                 AND q.value2 = t3.value2
              );

用于更新的 Rextester 演示

推荐阅读