SELECT语句中的SQL UPDATE在分区语句之上 [英] 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.

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