问SQL以确定要保留的行和要删除的行。

EN

with f as (
  select *,
    case when OrigFilename like '%*_%' escape '*' then 0 else 1 end del
  from t
)
delete from f
where del=1 
  and exists (select * from f f2 where f2.par=f.par and f2.del=0)

;WITH c_7_records AS (
      SELECT par
        FROM my_table
       WHERE origfilename LIKE '%_C_7_%'
), 
r_1_records AS (
      SELECT par
        FROM my_table
       WHERE origfilename LIKE '%_R_1%'
), 
records_to_remove AS (
      SELECT 
    DISTINCT mt.origfilename
        FROM my_table AS mt
        JOIN c_7_records AS cr ON mt.par = cr.par
        JOIN r_1_records AS rr ON mt.par = rr.par
       WHERE mt.origfilename NOT LIKE '%_C_7_%'
         AND mt.origfilename NOT LIKE '%_R_1%'
)
SELECT * FROM records_to_remove;