定义另一个表的计算列引用 [英] define a computed column reference another table

问题描述

我有两个数据库表，团队（ ID，NAME，CITY，BOSS，TOTALPLAYER ）和
Player （ ID，NAME，TEAMID，AGE ），两张桌子之间的关系是一对多的，一个团队可以有很多球员。

I have two database tables, Team (ID, NAME, CITY, BOSS, TOTALPLAYER) and Player (ID, NAME, TEAMID, AGE), the relationship between the two tables is one to many, one team can have many players.

我想知道是否有一种方法可以在团队中定义 TOTALPLAYER 列表是计算出来的吗？

I want to know is there a way to define a TOTALPLAYER column in the Team table as computed?

例如，如果有10个玩家的 TEAMID 为1，则 Team 表，其中 ID 为1的 TOTALPLAYER 列的值为10。如果添加一个播放器， TOTALPLAYER 列的值最多为11，我不需要显式分配值，而是由数据库生成。有人知道如何实现吗？

For example, if there are 10 players' TEAMID is 1, then the row in Team table which ID is 1 has the TOTALPLAYER column with a value of 10. If I add a player, the TOTALPLAYER column's value goes up to 11, I needn't to explicitly assign value to it, let it generated by the database. Anyone know how to realize it?

Thx提前。

BTW，数据库为SQL Server 2008 R2

BTW, the database is SQL Server 2008 R2

推荐答案

是的，您可以做到-您需要一个函数来计算球队的球员人数，并在计算列中使用该函数：

Yes, you can do that - you need a function to count the players for the team, and use that in the computed column:

CREATE FUNCTION dbo.CountPlayers (@TeamID INT)
RETURNS INT 
AS BEGIN
    DECLARE @PlayerCount INT

    SELECT @PlayerCount = COUNT(*) FROM dbo.Player WHERE TeamID = @TeamID

    RETURN @PlayerCount
END

，然后定义您的计算列：

and then define your computed column:

ALTER TABLE dbo.Team
ADD TotalPlayers AS dbo.CountPlayers(ID) 

现在，如果您选择时，每次为选定的每个团队调用该函数。该值不会保留在 Team 表中-每次您从 Team 表中进行选择时都会即时计算得出。

Now if you select, that function is being called every time, for each team being selected. The value is not persisted in the Team table - it's calculated on the fly each time you select from the Team table.

因为它的价值不存在，所以问题实际上是：它是否需要成为表上的计算列，还是可以根据需要使用存储的函数来计算玩家数？

Since it's value isn't persisted, the question really is: does it need to be a computed column on the table, or could you just use the stored function to compute the number of players, if needed?

这篇关于定义另一个表的计算列引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！