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

原创

jwsoft007 2022-08-19 09:40:33 博主文章分类：Sql Server ©著作权

©著作权归作者所有：来自51CTO博客作者jwsoft007的原创作品，请联系作者获取转载授权，否则将追究法律责任

-- 原始数据  
    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 ) )