使用另一个表中的数据创建计算列 [英] Create Computed Column using data from another table

问题描述

我有一个SQL Server 2008 R2数据库。该数据库有两个表，分别称为Pictures和PictureUse。

I have a SQL Server 2008 R2 database. This database has two tables called Pictures and PictureUse.

Picture表包含以下列：

Picture table has the following columns:

Id (int)   
PictureName (nvarchar(max)) 
CreateDate (datetime )  

PictureUse表包含以下列：

PictureUse table has the following columns :

Id (int) 
Pictureid (int) 
CreateDate (datetime )  

我需要在<$ c中创建一个计算列$ c>图片表，它告诉我该图片被点击了多少次。有帮助吗？

I need to create a computed column in the Picture table which tells me that how many times this picture has been clicked.any help ?

推荐答案

您可以为此创建用户定义的函数：

You can create a user-defined function for that:

CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT Count(id)
         FROM   PictureUse
         WHERE  PictureId = @PictureId)
  END 

然后可以像这样添加计算列：

The computed column can then be added like this:

ALTER TABLE dbo.Picture
ADD NofUses AS dbo.CountUses(Id)

但是，我宁愿为此：

CREATE VIEW PictureView
AS
  SELECT Picture.Id,
         PictureName,
         Picture.CreateDate,
         Count(PictureUse.Id) NofUses
  FROM   Picture
         JOIN PictureUse
           ON Picture.Id = PictureUse.PictureId
  GROUP  BY Picture.Id,
            PictureName,
            Picture.CreateDate 

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