如何使用计算字段声明这个SQL表?

create table Son
(
    ID int primary key identity(1,1),
    DateOfBirth datetime,
    constraint Age check (datediff(year,DateOfBirth,GetDate())>= 0),
    Name nvarchar(50) not null check (len(Name) >= 8) default('Sergio')
)

Right now I'm just checking it he Age column is bigger than 0, how can I declare the table if the Age column is meant to be calculated?

现在我只是检查它的年龄列大于0，如果要计算年龄列，我怎么能声明这个表呢?

2 个解决方案

#1

You can introduce a computed column:

你可以介绍一个计算列:

CREATE TABLE Son (
    ID int primary key identity(1,1),
    DateOfBirth datetime,
    Age as DATEDIFF(year,DateOfBirth,GetDate()) - CASE WHEN DATEPART(month,DateOfBirth) > DATEPART(month,GetDate()) THEN 1 WHEN DATEPART(month,DateOfBirth) = DATEPART(month,GetDate()) And DATEPART(day,DateOfBirth) > DATEPART(day,GetDate()) THEN 1 ELSE 0 END
/* Other columns */
)

I think that formula's right - DATEDIFF simple measures boundary transitions (31st Dec -> 1st Jan), so you need to compare months and days also, otherwise someone born on 31st Dec is 1 year old a day later.

我认为公式是正确的- DATEDIFF简单测量了边界跃迁(12月31日- 1月1日>)，所以你也需要比较月份和天数，否则出生在12月31日的人一天后就是1岁。

#2

I would start by creating a function to calculate the age. This gives you something that is reusable on other tables as well as being able to be used in scenarios where you might want to calculate a persons age on a specific date. For example, I'm looking to throw a party later this year, and I want to know everyone that will be of legal age on that date to send them an invitation.

我首先要创建一个函数来计算年龄。这为您提供了在其他表上可重用的东西，并且可以在您可能希望计算某一特定日期的人的年龄的场景中使用。例如，我打算在今年晚些时候举办一个聚会，我想知道在那个日子里每个人都有法定年龄，可以给他们发请帖。

Then create your table and add the calculated column that references that function. Add the check constraint for the age and the length of the Given Name, etc.

然后创建表并添加引用该函数的计算列。为给定名称的年龄和长度添加检查约束，等等。

You should be wary though, that by using a function, you cannot alter this function without dropping the references from the table (i.e. the computed column).

但是您应该注意，通过使用一个函数，您不能在不删除表(即计算列)中的引用的情况下修改这个函数。

CREATE FUNCTION dbo.sfn_GetAge (@BirthDate datetime, @CalcDate datetime)
RETURNS INT
AS
BEGIN

    DECLARE @theAge INT

    IF Month(@CalcDate) > Month(@BirthDate ) 
        SELECT @theAge= (Year(@CalcDate) - Year(@BirthDate ))

    ELSE IF Month(@CalcDate) < Month(@BirthDate ) 
        SELECT @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1)

    ELSE IF Day(@CalcDate) < Day(@BirthDate ) 
        SELECT @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1)

    ELSE 
        SELECT @theAge= (Year(@CalcDate) - Year(@BirthDate ))

    RETURN @theAge

END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Son](
    [ID] INT PRIMARY KEY IDENTITY(1,1),
    [GivenName] [nvarchar](50) NOT NULL,
    [Surname] [nvarchar](50) NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [Age]  AS ([dbo].[sfn_GetAge]([DateOfBirth],getdate()))
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Son] ADD  CONSTRAINT [DF_Son_GivenName]  DEFAULT (N'Sergio') FOR [GivenName]
GO

ALTER TABLE [dbo].[Son]  WITH CHECK ADD CONSTRAINT [CK_Son_Age] CHECK (([dbo].[sfn_GetAge]([DateOfBirth],getdate())>=(0)))
GO

ALTER TABLE [dbo].[Son] CHECK CONSTRAINT [CK_Son_Age]
GO

ALTER TABLE [dbo].[Son]  WITH CHECK ADD  CONSTRAINT [CK_Son_GivenName] CHECK  ((len([GivenName])>=(8)))
GO

ALTER TABLE [dbo].[Son] CHECK CONSTRAINT [CK_Son_GivenName]
GO

#1