sql 查询之排除某种特定的数据

-- 原始数据  
    SELECT 'LAB TEST' A,'RULE1' B,'TEST MSG' C,'TRUE' D  
    UNION ALL SELECT 'LAB TEST','RULE2','TEST MSG2',NULL  
    UNION ALL SELECT 'LAB TEST','RULE3','TEST MSG3',NULL  
    UNION ALL SELECT 'LAB TEST','RULE4','TEST MSG4',NULL  
    UNION ALL SELECT 'LAB TEST1','RULE1','TEST MSG1',NULL  
    UNION ALL SELECT 'LAB TEST','RULE5','TEST MSG5','TRUE'  
    UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG6',null  
    UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG7',null
    UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG8',null
      
      
       
     -- 当D为 true时，如果某种B值对应的记录，只有一行数据，则让这行数据出来；  
      -- 当D为 true时，如果某种B值对应的记录，有多行数据，则让 D=true的数据隐藏；  
      -- 当 D为 false 或 null时，数据都出来。  
      
     SELECT *  
     FROM  
     (  
         SELECT   
           *,  
           ROW_NUMBER() OVER(Partition by B ORDER BY ISNULL(D,0) ASC) AS RANK1  
         FROM  
         (  
            SELECT 'LAB TEST' A,'RULE1' B,'TEST MSG' C,'TRUE' D  
            UNION ALL SELECT 'LAB TEST','RULE2','TEST MSG2',NULL  
            UNION ALL SELECT 'LAB TEST','RULE3','TEST MSG3',NULL  
            UNION ALL SELECT 'LAB TEST','RULE4','TEST MSG4',NULL  
            UNION ALL SELECT 'LAB TEST1','RULE1','TEST MSG1',NULL  
            UNION ALL SELECT 'LAB TEST','RULE5','TEST MSG5','TRUE'  
            UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG6',null  
            UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG7',null
            UNION ALL SELECT 'LAB TEST','RULE6','TEST MSG8',null
        ) A  
    ) A  
    WHERE   D is NULL  
    OR  (  NOT ( D= 'TRUE' AND RANK1=2 ) )      

运行结果：