SQL Server：UPDATE 语句，其中 MAX 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6381352/
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 Server: UPDATE statement where MAX query
提问by corlettk
I'm doing a data migration in SQL Server 2008 R2. I'm a SQL-Server noob, but I know Ingres and MySql pretty well.
我正在 SQL Server 2008 R2 中进行数据迁移。我是 SQL Server 菜鸟，但我非常了解 Ingres 和 MySql。
I need to set "default values" for two new fields to "the current values" from another table. Here's my first naive attempt (how I'd do it in Ingres).
我需要将两个新字段的“默认值”设置为另一个表中的“当前值”。这是我的第一次幼稚尝试（我如何在 Ingres 中做到这一点）。
update rk_risk
set n_target_probability_ID = a.n_probability_ID
, n_target_consequence_ID = a.n_consequence_ID
from rk_assess a
WHERE a.n_assess_id = (
SELECT MAX(n_assess_id)
FROM rk_assess a2
WHERE a2.n_risk_id = a.n_risk_id
);
The above query executes without error in sequel, but it sets ALLthe n_target_probability_ID's & n_target_consequence_ID's to the same value... that of the OUTRIGHT last assessment (as apposed to "the last assessment OF THIS RISK").
上述查询在后续执行中没有错误，但它将所有n_target_probability_ID 和 n_target_consequence_ID 设置为相同的值...... OUTRIGHT 最后一次评估的值（与“此风险的最后一次评估”相对应）。
The rk_assesstable contains a complete history of assessment records for rk_risks, and my mission is to "default" the new target probability & consequence column of the risk table to the values from "the current" (i.e. the last) assessment record. The rk_assess.n_assess_idcolumn is an auto-incremented identifier (immutable once set), so the max-id should allways be the last-entered record.
该rk_assess表包含rk_risks的完整评估记录历史，我的任务是将风险表的新目标概率和后果列“默认”为“当前”（即最后一个）评估记录的值。该rk_assess.n_assess_id列是一个自动递增的标识符（设置后不可变），因此 max-id 应该始终是最后输入的记录。
I've had a bit of a search, both in google and SO, and tried a few different version of the query, but I'm still stuck. Here's a couple of other epic-fails, with references.
我在谷歌和 SO 中进行了一些搜索，并尝试了几个不同版本的查询，但我仍然被卡住了。这是其他一些史诗般的失败，有参考。
update rk_risk
set n_target_probability_ID = (select a.n_probability_ID from rk_assess a where a.n_assess_id = (select max(n_assess_id) from rk_assess a2 where a2.n_risk_id = a.n_risk_id) as ca)
, n_target_consequence_ID = (select a.n_consequence_ID from rk_assess a where a.n_assess_id = (select max(n_assess_id) from rk_assess a2 where a2.n_risk_id = a.n_risk_id) as ca)
;
http://stackoverflow.com/questions/6256844/sql-server-update-from-select
update r
set r.n_target_probability_ID = ca.n_probability_ID
, r.n_target_consequence_ID = ca.n_consequence_ID
from rk_risk r
join rk_assess a
on a.n_risk_id = r.n_risk_id
select r.n_risk_id
, r.n_target_probability_ID, r.n_target_consequence_ID
, ca.n_probability_ID, ca.n_consequence_ID
from rk_risk r
join rk_assess a
on a.n_risk_id = r.n_risk_id
http://stackoverflow.com/questions/4024489/sql-server-max-statement-returns-multiple-results
UPDATE rk_risk
SET n_target_probability_ID = ca.n_probability_ID
, n_target_consequence_ID = ca.n_consequence_ID
FROM ( rk_assess a
INNER JOIN (
SELECT MAX(a2.n_assess_id)
FROM rk_assess a2
WHERE a2.n_risk_id = a.n_risk_id
) ca -- current assessment
Any pointers would be greatly appreciated. Thank you all in advance, for even reading this far.
任何指针将不胜感激。提前感谢大家，甚至阅读了这么远。
Cheers. Keith.
干杯。基思。
回答by Jerad Rose
How about this:
这个怎么样：
update rk_risk
set n_target_probability_ID = a.n_probability_ID
, n_target_consequence_ID = a.n_consequence_ID
from rk_assess a
JOIN (
SELECT n_risk_id, MAX(n_assess_id) max_n_assess_id
FROM rk_assess
GROUP BY n_risk_id
) b
ON a.n_risk_id = b.n_risk_id AND a.n_assess_id = b.max_n_assess_id
WHERE a.n_risk_id = rk_risk.n_risk_id
回答by Conrad Frix
if you're using sql 2005 or greater you can in addition to Jerad's answer use the row_number function
如果您使用的是 sql 2005 或更高版本，除了 Jerad 的答案之外，您还可以使用 row_number 函数
With b
(
SELECT n_risk_id,
n_assess_id,
n_probability_ID,
n_consequence_ID,
row_number() over (partition by n_risk_id order by n_assess_id desc) row
FROM rk_assess
)
update rk_risk
set n_target_probability_ID = b.n_probability_ID
, n_target_consequence_ID = b.n_consequence_ID
from b
WHERE a.n_risk_id = rk_risk.n_assess_id
and row =1
Or CROSS JOIN
或交叉连接
update rk_risk
set n_target_probability_ID = b.n_probability_ID
, n_target_consequence_ID = b.n_consequence_ID
from rh_risk r
CROSS JOIN
(
SELECT TOP 1
n_risk_id,
n_assess_id,
n_probability_ID,
n_consequence_ID
FROM rk_assess
order by n_assess_id desc
WHERE a.n_risk_id = r.n_assess_id) b
回答by Aziz Shaikh
I tried this, looks like it is working:
我试过这个，看起来它正在工作：
update rk_risk
set n_target_probability_ID = a.n_probability_ID,
n_target_consequence_ID = a.n_consequence_ID
from rk_assess a, rk_risk r
WHERE a.n_risk_id = r.n_risk_id
and a.n_assess_id in (select MAX(n_assess_id) from rk_assess group by n_risk_id)
回答by Andrew Lazarus
I discovered this from another question on SO just today. The UPDATE-FROMconstruction is not standard SQL, and MySQL's non-standard version is different from Postgres's non-standard version. From the problem here, it looks like SQL Server follows Postgres.
我今天从另一个关于 SO 的问题中发现了这一点。该UPDATE-FROM建筑是不是标准的SQL和MySQL的非标准版本是从的Postgres的非标准版本不同。从这里的问题来看，SQL Server 似乎遵循 Postgres。
The problem, as Jerad points out in his edit, is that there is no link between the table being updated and the tables in the subquery. MySQL seems to create some implicit join here (on column names? in the other SO example, it was by treating two copies of the same table as the same, not separate).
正如 Jerad 在他的编辑中指出的那样，问题在于正在更新的表与子查询中的表之间没有链接。MySQL 似乎在这里创建了一些隐式连接（在列名上？在另一个 SO 示例中，它是通过将同一个表的两个副本视为相同而不是分开来处理的）。
I don't know if SQL Server allows windowing in the subquery, but if it does, I think you want
我不知道 SQL Server 是否允许在子查询中使用窗口，但如果允许，我想你想要
UPDATE rk_risk
set n_target_probability_ID = a.n_probability_ID
, n_target_consequence_ID = a.n_consequence_ID
from
( SELECT * FROM
( SELECT n_risk_id, n_probability_ID, n_consequence_ID,
row_number() OVER (PARTITION BY n_risk_id ORDER BY n_assess_ID DESC) AS rn
FROM rk_assess)
WHERE rn = 1) AS a
WHERE a.n_risk_id=rk_risk.n_risk_id;
