SQL UPDATE 中的 SELECT 排名超过 Partition 语句 [英] SQL UPDATE in a SELECT rank over Partition sentence

问题描述

这是我的问题，我有一个这样的表:

There is my problem, I have a table like this:

Company, direction, type, year, month, value, rank

当我创建表时，排名默认为 0，我想要的是使用此选择更新表中的排名:

When I create the table, rank is 0 by default, and what I want is to update rank in the table using this select:

SELECT company, direction, type, year, month, value, rank() OVER (PARTITION BY direction, type, year, month ORDER BY value DESC) as rank
FROM table1
GROUP BY company, direction, type, year, month, value
ORDER BY company, direction, type, year, month, value;

这个 Select 工作正常，但我找不到使用它来更新 table1 的方法

This Select is working fine, but I can't find the way to use it to update table1

我还没有找到任何用这种句子解决这样的问题的答案.如果有人能给我任何关于是否可以做的建议，我将非常感激.

I have not find any answer solving a problem like this with this kind of sentence. If someone could give me any advice about if it is posible to do or not I would be very grateful.

谢谢！

推荐答案

你可以加入子查询并进行UPDATE:

UPDATE table_name t2
SET 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
  ) t1
WHERE t1.company = t2.company
AND t1.direction = t2.direction;

将所需条件添加到谓词.

Add required conditions to the predicate.

或者，

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

You could use MERGE and keep that query in the USING clause:

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 rank
FROM table1
GROUP BY company,
  direction,
  TYPE,
  YEAR,
  MONTH,
  VALUE
ORDER 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 子句中添加必要条件.

Add required conditions in the ON clause.

这篇关于SQL UPDATE 中的 SELECT 排名超过 Partition 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！