sql - CASE in where 子句与 Col1<>Col2

Question

在下面的示例数据中，我需要选择所有记录

1. ModelOut=ModelOut (全部)
2. ModelIn<>ModelOut
3. ModelIn=ModelOut

问题是ModelIn<>ModelOut因为我不知道如何用 CASE 来做where 子句中的语句。

Declare @type int;
SET @type=2; --- ModelIn <> ModelOut  

Create table #Data(ID int, ModelIn varchar(10), ModelOut varchar(10))
INSERT INTO #Data
    SELECT 1 AS ID,'A' AS ModelIn, 'B' AS ModelOut
    UNION ALL 
    SELECT 2 AS ID,'B' AS ModelIn, 'B' AS ModelOut
    UNION ALL 
    SELECT 3 AS ID,'C' AS ModelIn, 'D' AS ModelOut
    UNION ALL 
    SELECT 4 AS ID,'D' AS ModelIn, 'D' AS ModelOut

SELECT * FROM #Data
WHERE ModelOut =
    CASE  @type
        WHEN 1 THEN ModelOut --- all
        WHEN 2 THEN ???      --- ModelIn <> ModelOut
        WHEN 3 THEN ModelIn  --- ModelOut = ModelIn
    END
;

Drop table #Data;

Answer 1

您的查询不需要CASE(事实上，我认为这只会让事情复杂化)。只需像这样编写您的查询:

SELECT * FROM #Data
WHERE (
    @type = 1 OR
   (@type = 2 AND (ModelIn <> ModelOut)) OR
   (@type = 3 AND (ModelOut = ModelIn))
);