SQL:仅当行具有相同 ID 的值时才排除

Question

要求:在一个表中，如果对于相同的ID，如果Only for those RegStatus in (Hardcopies,SoftCopies) 如果有一个状态'InvalidData'，那么只包括那一行，忽略其余的。但是如果对于相同的 ID，如果没有状态 'InvalidData' ，则不要忽略任何行

 with source as (
       select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status ' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
            select 'ABC' as ID , 'SoftCopies' as RegStatus , 'InValidData' as DocStatus, 'Articles of Association' as Name
         union all
            select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status Certificate for GB' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'MON' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'HardCopies' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
     union all
      select 'XYZ' as ID , 'AcceptanceReview' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
      select 'xyz' as ID , 'AcceptanceReview' as RegStatus , 'InValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
         select 'XYZ' as ID , 'PacketSubmitted' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
       )
     select * from source;

UPDATE :Requirement Update as below

如果 RegStatus 列显示任何没有 HardCopies 或 SoftCopies并且有 ABC Pending，那么每个唯一 ID 只需要一个条目，例如 EDG

如果 RegStatus 列显示任何没有 HardCopies 或 SoftCopies或 ABC Pending，然后保持原样 Example JFG

如果“RegStatus”显示:“硬拷贝”或“软拷贝”然后，我们需要为该 ID 下的每个文档创建多个条目，并且 DocStatus 状态为“无效数据”。示例周一

Answer 1

这是一种方法:

select * from source
qualify max(case when DocStatus = 'InValidData' and RegStatus in ('HardCopies', 'SoftCopies') then 1 else 0 end) over (partition by ID) = 0 
or DocStatus = 'InValidData'