我有一个sql Server 2008 R2数据库.该数据库有两个名为Pictures和PictureUse的表.
图片表包含以下列：
Id (int) PictureName (nvarchar(max)) CreateDate (datetime )
PictureUse表包含以下列：
Id (int) Pictureid (int) CreateDate (datetime )
我需要在Picture表中创建一个计算列,它告诉我这张图片被点击了多少次.有什么帮助吗？
解决方法@H_301_14@
您可以为此创建用户定义的函数：
CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
BEGIN
RETURN
(SELECT Count(id)
FROM PictureUse
WHERE PictureId = @PictureId)
END
然后可以像这样添加计算列：
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,Picture.CreateDate
CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
BEGIN
RETURN
(SELECT Count(id)
FROM PictureUse
WHERE PictureId = @PictureId)
END
然后可以像这样添加计算列：
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,Picture.CreateDate
