SELECT语句中的SQL UPDATE在分区语句之上

SELECT语句中的SQL UPDATE在分区语句之上

您可以加入 子查询 并执行 UPDATe ：

UPDATE table_name t2SET t2.rank=  SELECT t1.rank FROM(  SELECt company,    direction,    type,    YEAR,    MONTH,    value,    rank() OVER (PARTITION BY direction, type, YEAR, MonTH ORDER BY value DESC) AS rank  FROM table_name  GROUP BY company,    direction,    TYPE,    YEAR,    MONTH,    VALUE  ORDER BY company,    direction,    TYPE,    YEAR,    MONTH,    VALUE  ) t1WHERe t1.company = t2.companyAND t1.direction = t2.direction;

在谓词中添加所需条件。

或者，

您可以使用 MERGE 并将该查询保留在 USING 子句中：

MERGE INTO table_name t USING(SELECt company,  direction,  TYPE,  YEAR,  MONTH,  VALUE,  rank() OVER (PARTITION BY direction, TYPE, YEAR, MonTH ORDER BY VALUE DESC) AS rankFROM table1GROUP BY company,  direction,  TYPE,  YEAR,  MONTH,  VALUEORDER BY company,  direction,  TYPE,  YEAR,  MONTH,  VALUE) s ON(t.company = s.company AND t.direction = s.direction)WHEN MATCHED THEN  UPDATE SET t.rank = s.rank;

在ON子句中添加所需的条件。