删除非最高版本和修订的数据集

Question

我想完成MS Access SQL的反面：获取具有最高版本和修订的数据集

我有一个数据库表，(针对这个问题(有四列：

1. ID
2. 文件编号
3. Revison
4. 版本

每个文档都有1..n个修订，每个修订都有1.-n个版本。

删除所在的文档的SQL语句是什么

• 相同的文件编号和相同的修订版，但存在更高版本

• 或存在相同的文件编号和更高版本(包括所有版本(

  ID  Doc-No Rev Vers Should be deleted?
  1   Dok1   01  01   yes, because same Doc-No higher rev exists
  2   Dok1   01  02   yes, because same Doc-No higher rev exists
  3   Dok1   01  03   yes, because same Doc-No higher rev exists
  4   Dok1   02  01   yes, because same Doc-No and a higher vers for this rev exists
  5   Dok1   02  02   no, because Rev 02 / V02 is the highest for Doc-No Dok1
  6   Dok2   01  01   yes, because same Doc-No higher rev exists
  7   Dok2   02  01   yes, because same Doc-No higher rev exists
  8   Dok2   03  01   no, because Rev 03 / V01 is the highest for Doc-No Dok2
  

Answer 1

对于修订版和版本级别的两个聚合查询，如果使用INNER JOIN，原始的最高修订版和最高版本查询将更高效，而不是相关的子查询。前者为外部查询中的所有行计算一次，后者为每行计算一次：

SELECT d.*
FROM (documents d
INNER JOIN 
(SELECT sub_d.[Doc-No], MAX(sub_d.Rev) AS max_rev
FROM documents sub_d
GROUP BY sub_d.[Doc-No]) AS agg1
ON d.[Doc-No] = agg1.[Doc-No]
AND d.Rev = agg1.max_rev)
INNER JOIN 
(SELECT sub_d.[Doc-No], sub_d.Rev,
MAX(sub_d.Ver) AS max_ver
FROM documents sub_d
GROUP BY sub_d.[Doc-No], sub_d.Rev) AS agg2
ON d.[Doc-No] = agg2.[Doc-No]
AND d.Rev = agg2.rev    
AND d.Ver = agg2.max_ver

将DELETE翻译为：

DELETE DISTINCTROW d.*
FROM (documents d
INNER JOIN 
(SELECT sub_d.[Doc-No], MAX(sub_d.Rev) AS max_rev
FROM documents sub_d
GROUP BY sub_d.[Doc-No]) AS agg1
ON d.[Doc-No] = agg1.[Doc-No]
AND d.Rev = agg1.max_rev)
INNER JOIN 
(SELECT sub_d.[Doc-No], sub_d.Rev,
MAX(sub_d.Ver) AS max_ver
FROM documents sub_d
GROUP BY sub_d.[Doc-No], sub_d.Rev) AS agg2
ON d.[Doc-No] = agg2.[Doc-No]
AND d.Rev = agg2.rev
AND d.Ver = agg2.max_ver

Answer 2

你可以做：

delete from t
where t.id <> (select top (1) t2.id
from t as t2
where t2.doc_no = t.doc_num
order by t2.version desc, t2.revision desc, t2.id desc
);

当然，在尝试这样的delete之前，请先备份表。

Answer 3

以下内容应能达到预期结果：

delete from Table1 t1 
where exists
(
select 1 from Table1 t2 
where
t1.[Doc-No] = t2.[Doc-No] and 
(
t1.Rev < t2.Rev or 
(t1.Rev = t2.Rev and t1.Vers < t2.Vers)
)
)

(将Table更改为您的表名以及与您的数据不匹配的任何其他字段(

始终在运行delete查询之前备份您的数据-无法撤消！