如何在T-SQL创建表时用IF ELSE逻辑实现计算列？

要实现你需要的计算列逻辑，在T-SQL里得用CASE表达式（IF是流程控制语句，没法直接作为计算列的表达式）。下面是修改后的完整CREATE TABLE语句，重点看CloseLessEqualToOpen列的定义：

CREATE TABLE [dbo].[LowFloatStocks] ( 
    [Id] INT IDENTITY (1, 1) NOT NULL, 
    [Date] DATE NOT NULL, 
    [Ticker] NCHAR (10) NOT NULL, 
    [PreviousClose] DECIMAL (4, 2) DEFAULT ((4.00)) NOT NULL, 
    [OpeningPrice] DECIMAL (18, 2) NOT NULL, 
    [GainPercent] AS (round(([High]-[OpeningPrice])/[OpeningPrice],(4))*(100.0)) PERSISTED NOT NULL, 
    [GapPercent] AS (round(([OpeningPrice]-[PreviousClose])/[PreviousClose],(4))*(100.0)) PERSISTED NOT NULL, 
    [Spike] DECIMAL (18, 2) NOT NULL, 
    [1stSpike%] AS (round(([Spike]-[OpeningPrice])/[OpeningPrice],(4))*(100.0)) PERSISTED NOT NULL, 
    [High] DECIMAL (18, 2) NOT NULL, 
    [HighPercent] AS (round(([High]-[PreviousClose])/[PreviousClose],(4))*(100.0)) PERSISTED NOT NULL, 
    [Low] DECIMAL (18, 2) NOT NULL, 
    [LowPercent] AS (round(([Low]-[PreviousClose])/[PreviousClose],(4))*(100.0)) PERSISTED NOT NULL, 
    [Close] DECIMAL (18, 2) DEFAULT ((4)) NOT NULL, 
    [ClosePercent] AS (round(([Close]-[PreviousClose])/[PreviousClose],(4))*(100.0)) PERSISTED NOT NULL, 
    -- 自定义的条件计算列
    [CloseLessEqualToOpen] AS (CASE WHEN [Close] <= [OpeningPrice] THEN 'Yes' ELSE 'No' END) PERSISTED NCHAR(3) NOT NULL, 
    [CloseRed] NCHAR (3) NULL, 
    [ClosevHigh] AS (round(([High]-[Close])/[Close],(4))*(100)) PERSISTED NOT NULL, 
    [ClosevOpen] AS (round(([OpeningPrice]-[Close])/[OpeningPrice],(4))*(100.0)) PERSISTED NOT NULL, 
    [Catalyst] NVARCHAR (50) NOT NULL, 
    [Float] DECIMAL (18, 3) NOT NULL, 
    [Dilution] NCHAR (3) NOT NULL, 
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

关键细节说明： #

• 为什么用CASE而不是IF？因为IF是用来控制代码执行流程的，不能直接作为列的计算表达式，而CASE是专门用于返回条件结果的表达式，完全适配计算列场景。
• 我把原语句里的NULL改成了NOT NULL：这个计算列的结果永远是确定的（要么"Yes"要么"No"），没必要允许空值，这样能保证数据完整性。
• 保留PERSISTED关键字：它会让SQL Server把计算结果直接存储在表中，而不是每次查询时重新计算，能显著提升大表的查询性能。

如果你的表已经创建完成，不想重新建表，也可以用ALTER TABLE语句单独添加这个计算列：

ALTER TABLE [dbo].[LowFloatStocks]
ADD [CloseLessEqualToOpen] AS (CASE WHEN [Close] <= [OpeningPrice] THEN 'Yes' ELSE 'No' END) PERSISTED NCHAR(3) NOT NULL;

这样就能完美满足你的需求啦！

内容的提问来源于stack exchange，提问作者F.OLeary