SQL 使用另一个表中的数据创建计算列
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/13488822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Computed Column using data from another table
提问by Smartboy
I have a SQL Server 2008 R2 database. This database has two tables called Pictures and PictureUse.
我有一个 SQL Server 2008 R2 数据库。该数据库有两个表,称为图片和图片使用。
Picture table has the following columns:
图片表具有以下列:
Id (int)
PictureName (nvarchar(max))
CreateDate (datetime )
PictureUse table has the following columns :
PictureUse 表包含以下列:
Id (int)
Pictureid (int)
CreateDate (datetime )
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 ?
我需要在Picture
表中创建一个计算列,它告诉我这张图片被点击了多少次。有什么帮助吗?
回答by jeroenh
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)
However, I would rather make a view for this:
但是,我宁愿对此发表看法:
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
回答by Damien_The_Unbeliever
A computed column may only reference other columns in the same table. You could (as per jeroenh's answer) use a UDF, but the column won't be stored or be indexable and so it has to be recomputed every time the row is accessed.
计算列只能引用同一个表中的其他列。您可以(根据 jeroenh 的回答)使用 UDF,但该列不会被存储或可索引,因此每次访问该行时都必须重新计算。
You could create an indexed viewthat contains this information (if, as I suspect, it's just the count of rows from PictureUse
):
您可以创建一个包含此信息的索引视图(如果我怀疑它只是来自 的行数PictureUse
):
CREATE VIEW dbo.PictureStats
WITH SCHEMABINDING
AS
SELECT PictureID,COUNT_BIG(*) as Cnt from dbo.PictureUse
GO
CREATE UNIQUE CLUSTERED INDEX IC_PictureStats on dbo.PictureStats (PictureID)
Behind the scenes, SQL Server will effectively create a table that contains the results of this view, and every insert, update or delete to PictureUse
will maintain this results table automatically for you.
在幕后,SQL Server 将有效地创建一个包含此视图结果的表,并且每次插入、更新或删除PictureUse
都会自动为您维护此结果表。
回答by Joe G Joseph
you dont have to add the computed column to your table, because, after its updated,if the original table data got changed, then data become inconsistent , you can always use this select statement to get the column count, or create it as a view
您不必将计算列添加到您的表中,因为在更新后,如果原始表数据发生更改,则数据变得不一致,您始终可以使用此 select 语句来获取列数,或将其创建为视图
select p.id,count(*) as count
from Picture P
join PictureUse U
on p.id=u.Pictureid
group by p.id
回答by Ranish
This will work
这将工作
SELECT P.id
,P.PictureName
,COUNT(P.id) as [Count]
FROM Picture P
INNER JOIN PictureUse PU
ON P.id=PU.Pictureid
GROUP BY P.id,P.PictureName
回答by SRIRAM
try this
尝试这个
select count(distict pictureid) from pictureuse
inner join picture on picture.id=pictureuse.pictureid