oracle SELECT 等级中的 SQL UPDATE 高于 Partition 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28625110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL UPDATE in a SELECT rank over Partition sentence
提问by Roy90
There is my problem, I have a table like this:
这是我的问题，我有一张这样的表：
Company, direction, type, year, month, value, rank
When I create the table, rank is 0 by default, and what I want is to update rank in the table using this select:
创建表时，默认情况下排名为 0，我想要的是使用此选择更新表中的排名：
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;
This Select is working fine, but I can't find the way to use it to update table1
此 Select 工作正常，但我找不到使用它来更新 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.
我还没有找到用这种句子解决这样的问题的任何答案。如果有人能给我任何关于是否可行的建议，我将不胜感激。
Thanks!
谢谢！
回答by Lalit Kumar B
You could join the sub-queryand do an UPDATE:
您可以加入子查询并执行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.
向谓词添加所需的条件。
Or,
或者，
You could use MERGEand keep that query in the USINGclause:
您可以使用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 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;
Add required conditions in the ON clause.
在 ON 子句中添加必需的条件。
回答by Wernfried Domscheit
You can to it simply like this. It is not that commonly known but you can make UPDATEon a SELECT
你可以像这样简单地做到这一点。这不是众所周知的，但您可以UPDATE在SELECT
UPDATE
(SELECT
company, direction, TYPE, YEAR, MONTH, VALUE,
RANK() OVER (PARTITION BY direction, TYPE, YEAR, MONTH ORDER BY VALUE DESC) AS NEW_RANK
FROM table1) a
SET RANK = NEW_RANK;
