sql - 将IF转移到WHERE子句

以下作品

IF OBJECT_ID('TEMPDB..#Users') IS NOT NULL
BEGIN
    DROP TABLE #Users
END;

CREATE TABLE #Users(UserAccountKey INT);

IF @Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
BEGIN
   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE c.Name NOT IN ('X', 'Y', 'Z', 'J', 'Q')
      GROUP BY UserAccountKey
END
ELSE
BEGIN
   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE c.Name NOT IN ('X', 'Y')
      GROUP BY UserAccountKey;
END;

是否可以将其缩短为单个sql语句？可以将IF条件转移到WHERE子句吗？

最佳答案

处理此问题的最佳方法是将if转换为单个条件。我相信这将提供相同的结果:

   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE (@Period NOT IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
             and c.Name NOT IN ('X', 'Y'))
         or (@Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
            and c.Name NOT IN ('X', 'Y', 'Z', 'J', 'Q'))
      GROUP BY UserAccountKey

这一直困扰着我，因为我认为应该可以简化它，但是我不知道怎么做。我的原始答案与@Pirion相同，但是我更改了它，因为我意识到它不等同于原始脚本。下面是一个简化的脚本，我认为它提供的功能与我先前的答案相同:

   INSERT INTO #Users
      SELECT UserAccountKey
      FROM XXX c
      WHERE not (c.Name IN ('X', 'Y')
                 or (@Period IN ('Overall', 'Week1', 'Week2', 'Week3', 'Week4')
                     and c.Name IN ('Z', 'J', 'Q'))
      GROUP BY UserAccountKey

关于sql - 将IF转移到WHERE子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15956252/