使用另一个表中的数据创建计算列 [英] 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屋!
查看全文