主页 / dba / 问题 / 94978
Accepted
ΩmegaMan
Asked: 2015-03-12 06:56:09 +0800 CST

基于其他位列的计算列位

  • 772

从其他位列计算位列的公式是什么？

例如：

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          [BIT] NOT NULL, -- This is computed from other three columns.
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_HasValidHours]  
  DEFAULT ((0)) FOR [HasValidHours];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoDemerits]  
  DEFAULT ((0)) FOR [NoDemerits];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoAccidents]  
  DEFAULT ((0)) FOR [NoAccidents];

IsOk将根据 ,和的and -ing计算。HasValidHoursNoDemeritsNoAccidents

sql-server sql-server-2012

2 个回答

  1. Best Answer

    如果其他三位列都 = 1，否则 IsOk = 0，您的意思是 IsOk = 1？

    如果是这样： -

    CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          AS [HasValidHours] & [NoDemerits] & [NoAccidents],
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

    请参见&（按位与）（Transact-SQL）

    • 6

  2. 您正在寻找的表达式是（无论您是执行计算列还是外部UPDATE）：

    CASE WHEN HasValidHours = 1 AND NoDemerits = 1 AND NoAccidents = 1 THEN 1 ELSE 0 END
    • 2

相关问题