SQL Server computed column select from another表

I'm not to sure what the best way to go about this is, so i'll describe what the end goal is, and if a computed column is the answer then please help me go that route, or perhaps a better route.

我不确定最好的方法是什么，所以我将描述最终目标是什么，如果计算列是答案，那么请帮助我走那条路线，或者也许是更好的路线。

I have two tables:

我有两个表:

Orders

订单

OrderId
PackageId
MediaSpend
TotalAdViews (Computed column)

Packages

包

PackageId
BaseAdViews

Each order is assigned a package that comes with say 1,000 views, you can then buy more media spend to get more views. I wanted to create a column called TotalAdViews which would add BaseAdViews + MediaSpend. From my understanding if persistance is enabled the column won't need to recalculate every time it is queried, which could help performance.

每个订单都分配一个包含1000个视图的包，然后您可以购买更多的媒体开销以获得更多的视图。我想创建一个名为TotalAdViews的列，它将添加BaseAdViews + MediaSpend。根据我的理解，如果启用了持久化，那么每次查询时都不需要重新计算，这可能有助于性能。

How do I get a value from another table in my computed column? Or please suggest an alternate way of accomplishing my goal.

如何从计算列中的另一个表中获取值?或者请提出另一种实现我目标的方法。

3 个解决方案

#1

You won't be able to use columns from another table within a computed column expression. This is an extract from the MSDN documentation.

您将无法在计算列表达式中使用来自另一个表的列。这是MSDN文档的摘录。

A computed column is computed from an expression that can use other columns in the same table.

计算列是从可以使用同一表中的其他列的表达式计算出来的。

You mentioned that your motivation for using a computed column was to increase performance. There are a lot of restrictions but an indexed view might add value here.

您提到使用计算列的动机是提高性能。这里有很多限制，但是索引视图可以在这里添加值。

#2

I know this answer comes two years late, but just to help anyone who googles and finds this post:

我知道这个答案迟了两年，但这只是为了帮助那些在谷歌上搜索并找到这篇文章的人:

It is perfectly legal to define a user-defined function and use it as the computed value. This function may contain select statements from other tables.

定义用户定义的函数并将其用作计算值是完全合法的。这个函数可能包含来自其他表的select语句。

CREATE FUNCTION dbo.getSumAdViews(@packageId int)
RETURNS TABLE
AS
RETURN
    select SUM(BaseAdViews) from Packages where PackageId = @packageId

Then in your computed column, just use the expression dbo.getSumAdViews(PackageId)

然后在计算列中，只需使用表达式dbo.getSumAdViews(PackageId)

#3

if it is only for display purposes,why not create a view..

如果只是为了显示目的，为什么不创建视图。

select <<add other columns you need>> mediaspend+basicadviews as totaladviews
from 
orders o
join
packages p
on p.packageid=o.orderid

#1