MySQL / SQL：使用更新表本身的相关子查询进行更新

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)

对于每个类别，查找与其相关的项目的外观总和。例如，可以使用（按类别从表组中选择sum（“appearances”））
For each item
set the ratio value as the item's appearances divided by the sum found for the category above

对于每个项目，将比率值设置为项目的外观除以上面类别的总和

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?

有任何想法吗？

3 个解决方案

#1

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.

它工作得非常快。我也尝试过相关子查询，但速度要慢得多（数量级），所以我坚持使用连接。

#2

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其中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有一个合适的参考手册，所以获取正确的语法应该不难;）

#3

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

#1