SQL 基于不同表列的计算列公式
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2769007/
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
formula for computed column based on different table's column
提问by Adnan Manuel TüRKEN
Consider this table: c_const
考虑这个表: c_const
code | nvalue
--------------
1 | 10000
2 | 20000
and another table t_anytable
和另一张桌子 t_anytable
rec_id | s_id | n_code
---------------------
2 | x | 1
The goal is to have s_id
be a computed column, based on this formula:
目标是s_id
成为一个计算列,基于这个公式:
rec_id*(select nvalue from c_const where code=ncode)
This produces an error:
这会产生一个错误:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
在此上下文中不允许使用子查询。只允许使用标量表达式。
How can I calculate the value for this computed column using another table's column as an input?
如何使用另一个表的列作为输入来计算此计算列的值?
回答by marc_s
You could create a user-defined function for this:
您可以为此创建一个用户定义的函数:
CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT)
RETURNS INT
AS
SELECT @recid * nvalue
FROM c_const
WHERE code = @ncode
and then use that to define your computed column:
然后使用它来定义您的计算列:
ALTER TABLE dbo.YourTable
ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)
回答by Aaronaught
This seems to be more of a job for views (indexed views, if you need fast lookups on the computed column):
这似乎更像是视图的工作(索引视图,如果您需要对计算列进行快速查找):
CREATE VIEW AnyView
WITH SCHEMABINDING
AS
SELECT a.rec_id, a.s_id, a.n_code, a.rec_id * c.nvalue AS foo
FROM AnyTable a
INNER JOIN C_Const c
ON c.code = a.n_code
This has a subtle difference from the subquery version in that it would return multiple records instead of producing an error if there are multiple results for the join. But that is easily resolved with a UNIQUE
constraint on c_const.code
(I suspect it's already a PRIMARY KEY
).
这与子查询版本有细微的差别,因为如果连接有多个结果,它会返回多条记录而不是产生错误。但这很容易通过UNIQUE
约束来解决c_const.code
(我怀疑它已经是一个PRIMARY KEY
)。
It's also a lot easier for someone to understand than the subquery version.
对于某些人来说,它也比子查询版本更容易理解。
You can do it with a subquery and UDF as marc_s has shown, but that's likely to be highlyinefficient compared to a simple JOIN
, since a scalar UDF will need to be computed row-by-row.
您可以使用子查询和UDF做得一样marc_s已经显示,但是这很可能是高度相比简单低效的JOIN
,因为标量UDF将需要计算的行由行。