MySQL/SQL：使用更新表本身的相关子查询进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/839938/
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
MySQL/SQL: Update with correlated subquery from the updated table itself
提问by Roee Adler
I have a generic question that I will try to explain using an example.
我有一个通用问题，我将尝试使用示例进行解释。
Say I have a table with the fields: "id", "name", "category", "appearances" and "ratio"
假设我有一个包含以下字段的表：“id”、“name”、“category”、“appearances”和“ratio”
The idea is that I have several items, each related to a single category and "appears" several times. The ratio field should include the percentage of each item's appearances out of the total number of appearances of items in the category.
这个想法是我有几个项目，每个项目都与一个类别相关并且“出现”几次。比率字段应包括每个项目的出现占类别中项目总出现次数的百分比。
In pseudo-code what I need is the following:
在伪代码中，我需要的是以下内容：
For each category
find the total sum of appearances for items related to it. For example it can be done with (select sum("appearances") from table group by category)For each item
set the ratio value as the item's appearances divided by the sum found for the category above
对于每个类别，
找到与其相关的项目的总出现次数。例如，它可以用 (select sum("appearances") from table group by category)对于每个项目，
将比率值设置为项目的外观除以上述类别的总和
Now I'm trying to achieve this with a single update query, but can't seem to do it. What I thought I should do is:
现在我试图通过一个更新查询来实现这一点，但似乎无法做到。我认为我应该做的是：
update Table T
set T.ratio = T.appearances /
(
select sum(S.appearances)
from Table S
where S.id = T.id
)
But MySQL does not accept the alias T in the update column, and I did not find other ways of achieving this.
但是 MySQL 不接受更新列中的别名 T，我也没有找到其他方法来实现这一点。
Any ideas?
有任何想法吗？
回答by Roee Adler
Following the two answers I received (none of which was complete so I wrote my own), what I eventually did is as follows:
根据我收到的两个答案（没有一个是完整的，所以我自己写的），我最终做的如下：
UPDATE Table AS target
INNER JOIN
(
select category, appearances_sum
from Table T inner join (
select category as cat, sum(appearances) as appearances_sum
from Table
group by cat
) as agg
where T.category = agg.cat
group by category
) as source
ON target.category = source.category
SET target.probability = target.appearances / source.appearances_sum
It works very quickly. I also tried with correlated subquery but it was much slower (orders of magnitude), so I'm sticking with the join.
它工作得非常快。我也尝试过使用相关子查询，但速度要慢得多（数量级），所以我坚持使用连接。
回答by Frans Bouma
Use joins right after UPDATE: Reference Manual – 13.2.11 UPDATE Syntax
在 UPDATE 之后立即使用连接： 参考手册 – 13.2.11 UPDATE 语法
so UPDATE table1 inner join table2 on .... set table1.foo=value where table2.bla = someothervalue
所以 UPDATE table1 内连接 table2 on .... set table1.foo=value where table2.bla = someothervalue
With these kind of things, always look at the manual. MySql has a proper reference manual, so it shouldn't be that hard to get the right syntax ;)
有这些东西，总要看说明书。MySql 有一个合适的参考手册，所以获得正确的语法应该不难；)
回答by ahains
This is how it is done in mssql, I think mysql is the same or similar:
这是在mssql中是这样完成的，我认为mysql是相同或相似的：
create table T (id int, ratio float, appearances int)
insert T values (1, null, 2)
insert T values (1, null, 3)
update T
set ratio = cast(appearances as float)/ agg.appearancesSum
from T join (
select id, sum(appearances) as appearancesSum
from T
group by id
) as agg on t.id = agg.id
