MySQL/SQL:使用更新表本身的相关子查询进行更新 [英] MySQL/SQL: Update with correlated subquery from the updated table itself

问题描述

我有一个通用的问题，我将尝试用一个例子来解释.

I have a generic question that I will try to explain using an example.

假设我有一个包含以下字段的表格:id"、name"、category"、appearances"和ratio"

Say I have a table with the fields: "id", "name", "category", "appearances" and "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:

• 对于每个类别
  找到与其相关的项目的总出现次数.例如，它可以通过 (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

现在我试图通过一个更新查询来实现这一点，但似乎无法做到.我认为我应该做的是:

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    
)

但是 MySQL 不接受更新列中的别名 T，我也没有找到其他方法来实现这一点.

But MySQL does not accept the alias T in the update column, and I did not find other ways of achieving this.

有什么想法吗?

推荐答案

按照我收到的两个答案(没有一个是完整的，所以我自己写的)，我最终做了如下:

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.

这篇关于MySQL/SQL:使用更新表本身的相关子查询进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！