在同一个表上更新百分比计算

发布于 2024-10-07 20:24:02 字数 796 浏览 5 评论 0原文

我有一个这样的表：

ItemID      PersonID  Score      Percentage
==========================================
1             1       10         [ = 10 / 10+30 = 25%]
1             2       30         [ = 30 / 10+30 = 75%]
2             1       20         [ = 20 / 20+40 = 33%]
2             2       40         [ = 40 / 20+40 = 67%]

“百分比”的数据没有输入，而是按一定的时间间隔自动计算。计算公式是 Percentage = Score / Total Score of the ItemID

我没有使用“SELECT-do the maths-UPDATE”方法，而是尝试编写一条 SQL 来更新“Percentage”。

我尝试的是这样的：

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 WHERE t2.ItemID = t1.ItemID)
        );

但它不起作用。（错误代码：1093 您无法在 FROM 子句中指定用于更新的目标表“t1”）。

有什么想法吗？

原文

I have a table like this:

ItemID      PersonID  Score      Percentage
==========================================
1             1       10         [ = 10 / 10+30 = 25%]
1             2       30         [ = 30 / 10+30 = 75%]
2             1       20         [ = 20 / 20+40 = 33%]
2             2       40         [ = 40 / 20+40 = 67%]

The data on "Percentage" is not entered but is auto-calculated at certain time interval.
The calculation is Percentage = Score / Total Score of the ItemID

Instead of using "SELECT-do the maths-UPDATE" method, I'm trying to write a single SQL to update the "Percentage".

What I tried is like this:

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 WHERE t2.ItemID = t1.ItemID)
        );

But it doesn't work.(Error Code : 1093 You can't specify target table 't1' for update in FROM clause).

Any idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玩心态 2024-10-14 20:24:02

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 left join tb_temp AS t3 on t2.ItemID = t3.ItemID)
        );

UPDATE tb_temp AS t1 
    SET t1.Percentage = 
        CEIL( t1.Score / 
            (SELECT SUM(t2.Score) FROM tb_temp AS t2 left join tb_temp AS t3 on t2.ItemID = t3.ItemID)
        );

回复收藏 0 原文

旧人哭 2024-10-14 20:24:02

尝试：

  UPDATE tb_temp t1 
     JOIN (SELECT ItemId, SUM(t2.Score) TotalScore
           FROM tb_temp 
           group by ItemId) t2 ON t2.ItemID = t1.ItemID
  SET t1.Percentage = CEIL( t1.Score / t2.TotalScore)

try:

  UPDATE tb_temp t1 
     JOIN (SELECT ItemId, SUM(t2.Score) TotalScore
           FROM tb_temp 
           group by ItemId) t2 ON t2.ItemID = t1.ItemID
  SET t1.Percentage = CEIL( t1.Score / t2.TotalScore)

回复收藏 0 原文

~没有更多了~