sql - 不允许在另一个计算列中使用计算列 - 我怎样才能允许它

Question

CREATE TABLE [dbo].[tbl_Marks](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SID] [int] NOT NULL FOREIGN KEY REFERENCES tbl_Student(ID),
    [Year] [char](1) NOT NULL,
    [ExamType] [char](5) NOT NULL,
    [S1] [int] null constraint s1_marks check (s1 >=0 and s1<=25),
    [S2] [int] null constraint s2_marks check (s2 >=0 and s2<=25),
    [S3] [int] null constraint s3_marks check (s3 >=0 and s3<=25),
    [S4] [int] null constraint s4_marks check (s4 >=0 and s4<=25),
    [S5] [int] null constraint s5_marks check (s5 >=0 and s5<=25),
    [TotalMarks] AS (S1+S2+S3+S4+S5),
    [Avg] AS ((S1+S2+S3+S4+S5)/5),
    [Percentage] AS (((S1+S2+S3+S4+S5)/125.0)*100),
    [Rank] [tinyint] NULL,
    [Grade] [char](1) NULL,
    [CreatedBy] [varchar](25) NULL,
    [CreatedOn] [datetime] NULL,
    [ModifiedBy] [varchar](25) NULL,
    [ModifiedOn] [datetime] NULL,
    [Remarks] [varchar](500) NULL
    CONSTRAINT [PK_tbl_Marks] PRIMARY KEY CLUSTERED([ID] ASC)
);

我创建了 about 表，我想根据百分比显示成绩，我为此创建了函数，我删除了现有的列名为“Grade”，并使用 alter 命令重新添加了“Grade”列

ALTER TABLE tbl_marks DROP COLUMN Grade

ALTER TABLE tbl_Marks ADD Grade AS dbo.fn_CalculateGrade(percentage)

添加“等级”列时，我收到此错误：

消息 1759，级别 16，状态 0，第 47 行表“tbl_Marks”中的计算列“百分比”不允许在另一个计算列定义中使用。

这是我的功能：

CREATE FUNCTION dbo.fn_CalculateGrade(@Percentage DECIMAL)
RETURNS VARCHAR(10)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Grade VARCHAR(10)
    SET @Grade = (CASE WHEN ISNULL(@Percentage,0) BETWEEN 60.00 AND 70.99 THEN 'D'
  WHEN ISNULL(@Percentage,0) BETWEEN 71.00 AND 80.99 THEN 'C'
  WHEN ISNULL(@Percentage,0) BETWEEN 81.00 AND 90.99 THEN 'B'
  WHEN ISNULL(@Percentage,0) BETWEEN 91.00 AND 100.00 THEN 'A' END)

    RETURN @Grade

END
GO

而且我尝试使用 out 函数添加该查询：

  UPDATE tbl_Marks SET Grade= (CASE WHEN ISNULL(Percentage,0) BETWEEN 60.00 AND 70.99 THEN 'D'
  WHEN ISNULL(Percentage,0) BETWEEN 71.00 AND 80.99 THEN 'C'
  WHEN ISNULL(Percentage,0) BETWEEN 81.00 AND 90.99 THEN 'B'
  WHEN ISNULL(Percentage,0) BETWEEN 91.00 AND 100.00 THEN 'A' END)

我怎样才能在没有任何错误的情况下实现这一目标？

标签： sqlsql-serversql-server-2017