代码之家  ›  专栏  ›  技术社区  ›  whytheq

拿走EXISTS,但留在开关里

  •  2
  • whytheq  · 技术社区  · 11 年前

    如何修改最终脚本以同时具有排除开关?我不想使用 EXISTS 在最终脚本中:

    SELECT MyField = 'x' INTO #Data
    UNION SELECT MyField = 'y'
    UNION SELECT MyField = 'z'
    UNION SELECT MyField = 'j'
    UNION SELECT MyField = 'q'
    UNION SELECT MyField = NULL;
    
    SELECT MyField = 'j' INTO #Exclusions
    UNION  SELECT MyField = 'q'
    UNION SELECT MyField = NULL;
    
    
    DECLARE @ShowAll TINYINT = 1; -->>exceptions "switch" is turned ON so all data returned
    SELECT *
    FROM   #Data d
    WHERE  @ShowAll = 1 OR 
           (
           EXISTS
             (
             SELECT d.MyField 
             EXCEPT
             SELECT MyField FROM #Exclusions
             )
           );
    
    --can I simplify and get rid of the EXISTS but still include a switch based on @ShowAll ? 
    SELECT *
    FROM   #Data d
           EXCEPT
           SELECT MyField FROM #Exclusions;
    
    2 回复  |  直到 11 年前
        1
  •  3
  •   flup    11 年前
    SELECT *
    FROM   #Data d
           EXCEPT
           SELECT MyField FROM #Exclusions WHERE @ShowAll <> 1;
    

    http://sqlfiddle.com/#!6/d41d8/3125

        2
  •  0
  •   Andrew    11 年前

    将其封装在存储过程中,并将开关作为选项传递。