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.
是否可以将表中计算字段的计算结果应用于另一个表中的字段。
i.e.
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.
你有一个名为'mug'的桌子,这里有一个名为'color'的孩子(这会让我的英国头受伤,但供应商来自美国,你打算做什么?)而这反过来又有一个孩子叫''尺寸'。每个表都有一个名为sold的字段。
The size.sold increments by 1 for every mug of a particular colour and size sold.
对于销售的特定颜色和尺寸的每个马克杯,size.sold递增1。
You want color.sold to be an aggregate of SUM size.sold WHERE size.colorid = color.colorid
您希望color.sold是SUM size.sold WHERE size.colorid = color.colorid的聚合
You want mug.sold to be an aggregate of SUM color.sold WHERE color.mugid = mug.mugid
您希望mug.sold是SUM color.sold WHERE color.mugid = mug.mugid的聚合
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?
反正有没有制作mug.sold和color.sold只是自己解决,或者我将不得不与触发器捣乱?
2 个解决方案
#1
6
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/
#2
2
No, it is not possible to do this. A computed column can only be derived from the values of other fields on the same row. To calculate an aggregate off another table you need to create a view.
不,这是不可能的。计算列只能从同一行上其他字段的值派生。要计算另一个表的聚合,您需要创建一个视图。
If your application needs to show the statistics ask the following questions:
如果您的应用程序需要显示统计信息,请提出以下问题:
- Is it really necessary to show this in real time? If so, why? If it is really necesary to do this, then you would have to use triggers to update a table. This links to a short wikipedia article on denormalisation. Triggers will affect write performance on table updates and relies on the triggers being active.
- If it is only necessary for reporting purposes, you could do the calculation in a view or a report.
- If it is necessary to support frequent ad-hoc reports you may be into the realms of a data mart and overnight ETL process.
是否真的有必要实时显示?如果是这样,为什么?如果真的需要这样做,那么你必须使用触发器来更新表。这链接到关于非规范化的简短*文章。触发器将影响表更新的写入性能,并依赖于激活的触发器。
如果仅出于报告目的,您可以在视图或报告中进行计算。
如果有必要支持频繁的临时报告,您可能会进入数据集市和隔夜ETL过程的领域。
#1
6
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/
#2
2
No, it is not possible to do this. A computed column can only be derived from the values of other fields on the same row. To calculate an aggregate off another table you need to create a view.
不,这是不可能的。计算列只能从同一行上其他字段的值派生。要计算另一个表的聚合,您需要创建一个视图。
If your application needs to show the statistics ask the following questions:
如果您的应用程序需要显示统计信息,请提出以下问题:
- Is it really necessary to show this in real time? If so, why? If it is really necesary to do this, then you would have to use triggers to update a table. This links to a short wikipedia article on denormalisation. Triggers will affect write performance on table updates and relies on the triggers being active.
- If it is only necessary for reporting purposes, you could do the calculation in a view or a report.
- If it is necessary to support frequent ad-hoc reports you may be into the realms of a data mart and overnight ETL process.
是否真的有必要实时显示?如果是这样,为什么?如果真的需要这样做,那么你必须使用触发器来更新表。这链接到关于非规范化的简短*文章。触发器将影响表更新的写入性能,并依赖于激活的触发器。
如果仅出于报告目的,您可以在视图或报告中进行计算。
如果有必要支持频繁的临时报告,您可能会进入数据集市和隔夜ETL过程的领域。