SQL 如何创建从另一列计算的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13626331/
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
How do I create column calculated from another column?
提问by Uday Vaswani
I need to create a column agein a SQL Server database.
我需要age在 SQL Server 数据库中创建一列。
The values of this column should be calculated based on the values of the column DOB.
该列的值应根据该列的值进行计算DOB。
Also its values should increment as Ageincreases.
它的值也应该随着Age增加而增加。
回答by SchmitzIT
You should use a computed column to solve this problem. Something with a definition similar to this:
您应该使用计算列来解决此问题。定义与此类似的东西：
ALTER TABLE Customers ADD Age AS datediff(year, DOB ,getdate())
Original statement taken from and further information available at BlackWasp.
BlackWasp 的原始声明和更多信息。
Edit:
编辑：
MSDN explains computed columns as:
MSDN 将计算列解释为：
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it. For more information, see Creating Indexes on Computed Columns.
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change > in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.
The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.
计算列是根据可以使用同一表中其他列的表达式计算得出的。表达式可以是一个非计算列名、常量、函数，以及由一个或多个运算符连接的这些的任意组合。表达式不能是子查询。
除非另有说明，计算列是物理上未存储在表中的虚拟列。每次在查询中引用它们时，都会重新计算它们的值。数据库引擎在 CREATE TABLE 和 ALTER TABLE 语句中使用 PERSISTED 关键字将计算列物理存储在表中。当作为其计算一部分的任何列发生更改时，它们的值也会更新。通过将计算列标记为 PERSISTED，您可以在确定性但不精确的计算列上创建索引。此外，如果计算列引用 CLR 函数，则数据库引擎无法验证该函数是否真正具有确定性。在这种情况下，计算列必须是 PERSISTED 以便可以在其上创建索引。有关更多信息，请参阅在计算列上创建索引。
计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何其他可以使用正则表达式的位置，但以下情况除外：
用作 CHECK、FOREIGN KEY 或 NOT NULL 约束的计算列必须标记为 PERSISTED。如果计算列值由确定性表达式定义并且索引列中允许结果的数据类型，则计算列可用作索引中的键列或任何 PRIMARY KEY 或 UNIQUE 约束的一部分。
例如，如果表具有整数列 a 和 b，则可以对计算列 a + b 进行索引，但无法对计算列 a + DATEPART(dd, GETDATE()) 进行索引，因为值可能会在后续调用中更改 >。
计算列不能是 INSERT 或 UPDATE 语句的目标。
数据库引擎根据使用的表达式自动确定计算列的可为空性。即使只存在不可为空的列，大多数表达式的结果也被认为是可空的，因为可能的下溢或溢出也会产生空结果。使用带有 AllowsNull 属性的 COLUMNPROPERTY 函数来调查表中任何计算列的可为空性。可以通过指定 ISNULL(check_expression, constant) 将可以为 null 的表达式转换为不可为 null 的表达式，其中常量是替换任何 null 结果的非空值。
Source: MSDN - Computed Columns
来源：MSDN - 计算列
回答by Wilmer
Code snippet
代码片段
ALTER TABLE
TheTable
ADD
DOB AS
CASE
WHEN
MONTH(Birth) > MONTH(ISNULL(Death, SYSDATETIME()))
OR (
MONTH(Birth) = MONTH(ISNULL(Death, SYSDATETIME()))
AND DAY(Birth) >= DAY(ISNULL(Death, SYSDATETIME()))
)
THEN
DATEDIFF(YEAR, Birth, ISNULL(Death, SYSDATETIME())) - 1
ELSE
DATEDIFF(YEAR, Birth, ISNULL(Death, SYSDATETIME()))
END
回答by kasim
Create Table with auto-generated column,
使用自动生成的列创建表，
CREATE TABLE Person2
(Id int IDENTITY(1,1) NOT NULL, Name nvarchar(50),
DOB date, Age AS DATEDIFF(YEAR, DOB ,GETDATE()) )
回答by t-clausen.dk
This is the correct way of getting the age:
这是获取年龄的正确方法：
alter table <yourtable> add age as datediff(year, DOB, getdate())- case when month(DOB)*32 + day(DOB) > month(getdate()) * 32 + day(getdate()) then 1 else 0 end
