嵌套查询 Microsoft Access

发布于 2024-11-04 04:27:19 字数 548 浏览 1 评论 0原文

我有一张交易表。该表是多个供应商进行的多笔交易和多个交易金额。如果供应商交易金额是该供应商平均交易金额的两倍以上，我需要更新该表。到目前为止，我想出了以下错误的代码：

Update tblTransaction
SET VariabilityIndicator = 1
WHERE transactionNumber IN
(Select transactionNumber
From tblTransaction
GROUP BY VendorName
HAVING transactionAmount >= AVG(transactionAmount*2))

上面的代码显然是错误的。我想出了一个可能可以嵌套的语句：

SELECT AVG(transactionAmount) VendorName
FROM tblTransaction
GROUP BY VendorName

这应该返回所有 VendorNames 及其平均交易金额。我如何嵌套它，以便我可以将 transactionAmount 与供应商名称匹配的平均值进行比较？

原文

I have a table of transactions. The table was multiple vendors with multiple transactions with multiple transaction amounts. I need to update the table if a vendors transaction is more than double the transaction amount average for that vendor. So far I came up with the following code which is wrong:

Update tblTransaction
SET VariabilityIndicator = 1
WHERE transactionNumber IN
(Select transactionNumber
From tblTransaction
GROUP BY VendorName
HAVING transactionAmount >= AVG(transactionAmount*2))

The code above is clearly wrong. I came up with a statement that possibly could be nested:

SELECT AVG(transactionAmount) VendorName
FROM tblTransaction
GROUP BY VendorName

This should return all VendorNames with their average transaction amounts. How can I nest this so I can compare a transactionAmount to the average where the vendor names match??

分享到QQ

分享到微博

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

发布评论

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

爱她像谁 2024-11-11 04:27:19

Update tblTransaction
SET VariabilityIndicator = 1
WHERE transactionNumber IN
(
    Select T.TransactionNumber
    from tblTransaction as T
    Where T.transactonAmount > 2 * (
      Select AVG(transactionAmount)
      From tblTransaction as A
      Where A.VendorName = T.VendorName
    )
)

您可能需要检查您的业务逻辑，以将正在评估的 transactionAmount 从平均计算中排除。此外，您可能不想匹配供应商名称，而是匹配某种 ID 号/主键值。

Update tblTransaction
SET VariabilityIndicator = 1
WHERE transactionNumber IN
(
    Select T.TransactionNumber
    from tblTransaction as T
    Where T.transactonAmount > 2 * (
      Select AVG(transactionAmount)
      From tblTransaction as A
      Where A.VendorName = T.VendorName
    )
)

You may want to check your business logic to exclude the transactionAmount being evaluated from the average calculation. Also you may not want to match on vendor name, but on some sort of ID number/Primary key value.

回复收藏 0 原文

~没有更多了~