从另一个表字段的值的总和的SQL Server 2005计算列结果 [英] SQL Server 2005 Computed Column Result From Aggregate Of Another Table Field's Value

问题描述

很抱歉，问题标题太长了。

Sorry for the long question title.

我想我是这个失败者中的一个失败者，但机会不多。

I guess I'm on to a loser on this one but on the off chance.

是否可以使表中计算字段的计算成为应用于另一个表中字段的聚合函数的结果。

Is it possible to make the calculation of a calculated field in a table the result of an aggregate function applied to a field in another table.

ie

您有一个名为 mug的表，其中有一个名为 color的孩子（这使我的英国头部受伤，但卖方来自美国，您打算怎么做？）然后又有了一个叫大小的孩子。每个表都有一个称为sold的字段。

You have a table called 'mug', this has a child called 'color' (which makes my UK head hurt but the vendor is from the US, what you going to do?) and this, in turn, has a child called 'size'. Each table has a field called sold.

size.sold对于出售的每种特定颜色和大小的杯子，sold都会增加1。

The size.sold increments by 1 for every mug of a particular colour and size sold.

您希望color.sold成为SUM size.sold的总和。在其中WHERE size.colorid = color.colorid

You want color.sold to be an aggregate of SUM size.sold WHERE size.colorid = color.colorid

您想使mug.sold成为总和color.sold哪里WHERE color.mugid = mug.mugid

You want mug.sold to be an aggregate of SUM color.sold WHERE color.mugid = mug.mugid

反正还是有个使cup.sold和color.sold自己动手做还是我要去？

Is there anyway to make mug.sold and color.sold just work themselves out or am I going to have to go mucking about with triggers?

推荐答案

您不能让计算列直接引用其他表，但是您必须可以让它引用用户定义的函数。这是实现此类解决方案的示例的链接。

you can't have a computed column directly reference a different table, but you can have it reference a user defined function. here's a link to a example of implementing a solution like this.

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/

这篇关于从另一个表字段的值的总和的SQL Server 2005计算列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！