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 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

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

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在update列中不接受别名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屋！