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

问题描述

我有两个数据库表，Team(ID、NAME、CITY、BOSS、TOTALPLAYER)和球员(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.

我想知道有没有办法将 Team 表中的 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?

提前致谢.

顺便说一句，数据库是 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

然后定义你的计算列:

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屋！