计算两列的百分比并写回表

发布于 2024-08-09 10:14:43 字数 566 浏览 8 评论 0原文

使用临时表：

DECLARE @Results TABLE (
    CDA     varchar(60),
    Found       int default 0,
    Accepted    int default 0,
    Percentage  decimal(3,0) default 0.0,
)

如何获取已填充的 Found 和 Accepted 列并将其写回 Pecentage 列？

我有：

UPDATE @Results SET Percentage = (
    SELECT (((Accepted * 1.00) / Found) * 100) FROM @Results 
)

如果您选择 SELECT 行（注释掉 UPDATE），它会返回正确的值。然而，在 UPDATE 的上下文中，它会失败，子查询返回超过 1 个值。当子查询跟在 =、!=、<、<=、>、>= 后面或子查询用作表达式时，这是不允许的。该语句已终止。

如何将值写回正确的行？

原文

With a temporary table as:

DECLARE @Results TABLE (
    CDA     varchar(60),
    Found       int default 0,
    Accepted    int default 0,
    Percentage  decimal(3,0) default 0.0,
)

How do you take populated Found and Accepted columns and write it back to the Pecentage column?

I have:

UPDATE @Results SET Percentage = (
    SELECT (((Accepted * 1.00) / Found) * 100) FROM @Results 
)

Which if you take the SELECT line (comment out the UPDATE) it returns the correct values. However in the context of the UPDATE it fails with Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

How do I write the values back to the correct row?

分享到QQ

分享到微博

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

发布评论

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

谁把谁当真 2024-08-16 10:14:43

UPDATE Result
   SET Percentage = Accepted / Found * 100

虽然 FOUND 和 ACCEPTED 定义为 INT，但它不起作用，在除法时使用强制转换或将它们声明为与 PERCENTAGE 相同的类型。

UPDATE Result
   SET Percentage = Accepted / Found * 100

It won't work though with FOUND and ACCEPTED defined as INT, use cast when dividing or declare them with the same type as PERCENTAGE.

回复收藏 0 原文

两人的回忆 2024-08-16 10:14:43

您需要一个 where 子句，例如（未经测试）

UPDATE Result oResult
  SET Percentage = (SELECT Accepted / Found * 100
                      FROM Result iResult 
                     WHERE iResult.cda = oResult.cda);

但是，如果这是计算出来的，您可能不希望将其作为列，而只是将其添加到任何查询中。请注意，大多数数据库都有百分比函数。

You need a where clause, e.g. (untested)

UPDATE Result oResult
  SET Percentage = (SELECT Accepted / Found * 100
                      FROM Result iResult 
                     WHERE iResult.cda = oResult.cda);

However, if this is calculated you may wish not to have this as a column and just add it to any queries instead. Note, most databases have a percent function.

回复收藏 0 原文

~没有更多了~