分组更新 [英] Update with group by

问题描述

我为似乎是简单的UPDATE语句而感到困惑.

I'm stumped on what seemed to be a simple UPDATE statement.

我正在寻找使用两个值的UPDATE.第一个(a)用于分组，第二个(b)用于查找各个组内的局部最小值.另外，b上有一个阈值:任何值1或更小都应保持不变.

I'm looking for an UPDATE that uses two values. The first (a) is used to group, the second (b) is used to find a local minimum of values within the respective group. As a little extra there is a threshold value on b: Any value 1 or smaller shall remain as it is.

drop table t1;
create table t1 (a number, b number);
insert into t1 values (1,0);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (2,2);
insert into t1 values (3,1);
insert into t1 values (3,2);
insert into t1 values (3,3);
insert into t1 values (4,1);
insert into t1 values (4,3);
insert into t1 values (4,4);
insert into t1 values (4,5);


-- 1,0 -> 1,0
-- 1,1 -> 1,1
-- 2,1 -> 2,1
-- 2,2 -> 2,2
-- 3,1 -> 3,1
-- 3,2 -> 3,2
-- 3,3 -> 3,2  <-
-- 4,1 -> 4,1
-- 4,3 -> 4,3  <-
-- 4,4 -> 4,3  <-
-- 4,5 -> 4,3  <-

显然不够:

update t1 x
   set b = (select min(b) from t1 where b > 1)
;

例如，无论我尝试哪种更复杂的方法

Whatever more complicated stuff I try, e.g.

UPDATE t1 x
   set (a,b) = (select distinct a,b from (
                    select a, min(b) from t1 where b > 1 group by a)
               )
;

我明白了 SQL-Fehler:ORA-01427:对Eine Zeile的不信任 01427. 00000-单行子查询返回多个行"

I get SQL-Fehler: ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile 01427. 00000 - "single-row subquery returns more than one row"

这并不奇怪，因为我需要为a的每个值都排一行.

which is not overly surprising as I need a row for each value of a.

我当然可以编写带有游标循环的PL/SQL过程，但是可以在一个优雅的SQL语句中实现吗?也许使用分区依据?

Of course I could write a PL/SQL Procedure with a cursor loop but is it possible in a single elegant SQL statement? Maybe using partition by?

推荐答案

您的问题有点令人困惑. 您说您想将值b设置为列b所在的分区a的最小值，而包含b = 1的行应保持不变.

Your question is a bit confusing. You say that you would like to set value b to a minimum value from partition a that column b is in row with, while the rows containing b = 1 should remain untouched.

根据您在问题中看到的注释(我假设它是您的预期输出)，您还希望获得分区中1之后的最小值-因此，您基本上希望得到b的最小值大于1.

From what I can see in your question as comments (I assume it's your expected output) you also want to get the minimum value that follows 1 within a partition - so you basically want the minimum value of b that is greater than 1.

下面是执行此操作的SQL查询

Below is SQL query that does this

UPDATE t1 alias
  SET b = (
    SELECT min(b) 
    FROM t1 
    WHERE alias.a = t1.a 
      AND t1.b > 1 -- this would get the minimum value higher than 1
    GROUP BY a
  )
  WHERE alias.b > 1 -- update will not affect rows with b <= 1

更新后输出

 a | b 
---+---
 1 | 0
 1 | 1
 2 | 1
 2 | 2
 3 | 1
 3 | 2
 3 | 2
 4 | 1
 4 | 3
 4 | 3
 4 | 3

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