它将冗余数据选择到一个游标中并根据（ＬａｓｔＮａｍｅＦｉｒｓｔＮａｍｅ）来分组（在我们这个方案中）然后打开游标然后循环地取出每一行然后用与先前的取出的键值进行比较如果这是第一次取出这个值或者这个值不是冗余键那么跳过这个记录然后取下一个不然的话这就是这个组中的冗余记录所以删掉它．
让我们运行一下这个存储过程
BEGIN
DeleteDuplicates;
END;
/
SELECT LastName FirstName COUNT(*)
FROM Customers
GROUP BY LastName FirstName
HAVING COUNT(*) > ;
最后一个查询语句没有返回值所以冗余数据没有了从表中取冗余数据的过程完全是由定义在csr_Duplicates 这个游标中的ＳＱＬ语句来实现的ＰＬ／ＳＱｌ只是用来实现删除冗余数那么能不能完全用ＳＱＬ语句来实现呢？
二．ＳＱＬ解决方案使用ＲＡＮＫ（）删除冗余数据Ｏｒａｃｌｅi分析函数ＲＡＮＫ（）来枚举每一个组中的元素在我们的方案中我们应用这个方案我们使用这个函数动态的把冗余数据连续的排列起来加上编号组由Ｐａｒｔｉｎｔｉｏｎｂｙ这个语句来分开然后用Ｏｒｄｅｒｂｙ进行分组SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) SeqNumber
FROM Customers
ORDER BY LastName FirstName;
SQL
Listing Output of single SQL statement that uses RANK()
显示的是根据记录的条数的个数来显示尤其对于冗余数据
ID LASTNAME FIRSTNAME SEQNUMBER
Blake Becky
Blue Don
Bradley Tom
Chang Jim
Griffith David
Hill Larry
King Chuck
Krieger Jeff
Krieger Jeff
Krieger Jeff
Loney Julie
Lord Don
Mason Paul
Monroe John
Simon Michael
Simon Michael
Stone Tony
Stone Tony
Stone Tony
Stone Tony
Stone Tony
我们可以看一到ＳｅｑＮｕｍｂｅｒ这一列中的数值冗余数据是根据ＩＤ号由小到大进行的排序所有的冗余数据的ＳｑｌＮｕｍｂｅｒ都大于一所有的非冗余数据都等于一所以我们取自己所需删除那么没用的SELECT ID LastName FirstName
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > ;
SQL
Listing 冗余键的键值
有七行必须被删除
ID LASTNAME FIRSTNAME
Krieger Jeff
Krieger Jeff
Simon Michael
Stone Tony
Stone Tony
Stone Tony
Stone Tony
rows selected这显示有七行需要删除还是用上一个表我测试了一下这个代码它用了７７秒种就删除了所有的数据准备好了用Ｓｑｌ语句来删除冗余数据版本一它执行了秒
DELETE
FROM CUSTOMERS
WHERE ID IN
(SELECT ID
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > );
我们可以看到最后的两行语句对表中的数据进行了排序这不是有效的所以我们来优化一下最后一个查询语句把Ｒａｎｋ（）函数应用到只含有冗余数据的组而不是所有的列下面这个语句是比较有效率的虽然它不像上一个查询那样精简SELECT ID LastName FirstName
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID LastName FirstName
FROM Customers
WHERE (LastName FirstName) IN (SELECT LastName FirstName
FROM Customers
GROUP BY LastName FirstName
HAVING COUNT(*) > )))
WHERE SeqNumber > ;
选择冗余数据只用了２６秒钟这样就提高了％的性能这样就提高了将这个作为子查询的删除查询的效率
DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID LastName FirstName
FROM Customers
WHERE (LastName FirstName) IN (SELECT LastName FirstName
FROM Customers
GROUP BY LastName FirstName
HAVING COUNT(*) > )))
WHERE SeqNumber > );
现在只用了秒钟的就完成的上面的任务比起上一个秒这是一个很大的进步相比之下存储过程用了秒这样存储过程有些慢了使用ＰＬ／ＳＱＬ语句我们和我们以上的代码会得到更好的更精确的代码和提高你代码的执行效率虽然对于从数据库中枚举数据ＰＬ／ＳＱＬ对于Ｓｑｌ两者没有什么差别但是对于数据的比较上ＰＬ／ＳＱＬ就比ＳＱＬ要快很多但是如果冗余数据量比较小的话我们尽量使用ＳＱＬ而不使用ＰＬ／ＳＱＬ如果你的数据表没有主键的话那么你可以参考其它技术
Ｒａｎｋ（）其它的方法
使用Ｒａｎｋ（）函数你可以对选择你所保留的数据（或者是小ＩＤ的或者是大ＩＤ的就由ＲＥＣＤａｔｅ这个列来决定这种情况下你可以把ＲＥｃｄａｔｅ加入到（Ｏｒｄｅｒｂｙ）子句中倒序或者正序
这是一种保留最大Ｉｄ的一种解决方案
DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName FirstName ORDER BY RecDate DESC ID) AS SeqNumber
FROM
(SELECT ID LastName FirstName RecDate
FROM Customers
WHERE (LastName FirstName) IN (SELECT LastName FirstName
FROM Customers
GROUP BY LastName FirstName
HAVING COUNT(*) > )))
WHERE SeqNumber > );
这种技术保证了你可以控制每一个表中的保留的组假设你有一个数据库有一个促销或者有一个折扣信息比如一个团体可以使用这种促销５次或者个人可以使用这个折扣三次为了指出要保留的组的个数你可以在where 和having子句中进行设置那么你将删除所有大于你
设置有数的冗余组
DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID LastName FirstName
FROM Customers
WHERE (LastName FirstName) IN (SELECT LastName FirstName
FROM Customers
GROUP BY LastName FirstName
HAVING COUNT(*) > )))
WHERE SeqNumber > );
As you can see using the RANK() function allows you to eliminate duplicates in a
single SQL statement and gives you more capabilities by extending the power of
your
queries
正如你所见使用Ｒａｎｋ（）可以消除冗余数据而且能给你很大的可伸展性
