我有一个表,其中包含3个名为services (service1、service2、service3)的键列和其他值列。我想从表中删除所有重复的记录,基于3个关键字段的组合(以任何顺序)。例如,关键字段为'car,truck,bike‘和'bike,car,truck’的记录是重复的记录,尽管字段值的位置不同。注:编辑了我的答案,以便在评论中获得更详细的陈述。
发布于 2018-04-24 14:11:17
听起来好像这个表设计得很糟糕,所以我会考虑完全重构。
但要按原样处理它(并且不使用游标),我认为最快的方法是列出每个可能的排列来查找重复项,然后分配一个行号。
示例:
有6个数字1 2 3的排列:
123, 132, 213, 231, 312, 321你的“自行车”、“汽车”、“卡车”的例子也是如此:
'bike' 'car' 'truck', 'car' 'bike' 'truck', ... etc.因此,我们希望将表中的数据划分为重复的组(基于所有可能的排列),并为分区中的每一行分配一个行号。
Click here for a working example in SqlFiddle
示例表和数据:
CREATE TABLE services
( service1 VARCHAR(10),
service2 VARCHAR(10),
service3 VARCHAR(10)
);
--these first three values duplicate each other. They should end up
--partitioned together in our query
INSERT INTO services VALUES ('bike', 'car', 'truck');
INSERT INTO services VALUES ('truck', 'bike', 'car');
INSERT INTO services VALUES ('car', 'truck', 'bike');
--this fourth value should be in a partition on it's own
INSERT INTO services VALUES ('moped', 'car', 'truck');运行此查询以查看分区的结果。这实际上是说,为三列等于自身的不同排列的所有行创建一个分区:
SELECT s.*,
Row_number() over(PARTITION BY (SELECT DISTINCT 1
FROM services s1
WHERE ( s1.service1 = s.service1
AND s1.service2 = s.service3
AND s1.service3 = s.service2)
OR ( s1.service1 = s.service2
AND s1.service2 = s.service1
AND s1.service3 = s.service3)
OR ( s1.service1 = s.service2
AND s1.service2 = s.service3
AND s1.service3 = s.service1)
OR ( s1.service1 = s.service3
AND s1.service2 = s.service1
AND s1.service3 = s.service2)
OR ( s1.service1 = s.service3
AND s1.service2 = s.service2
AND s1.service3 = s.service1) )
ORDER BY (null)) AS rownumber
FROM services s;现在有了结果,您可以看到需要删除rownumber大于1的所有行:
DELETE
FROM (SELECT s.*,
Row_number() over(PARTITION BY (SELECT DISTINCT 1
FROM services s1
WHERE ( s1.service1 = s.service1
AND s1.service2 = s.service3
AND s1.service3 = s.service2)
OR ( s1.service1 = s.service2
AND s1.service2 = s.service1
AND s1.service3 = s.service3)
OR ( s1.service1 = s.service2
AND s1.service2 = s.service3
AND s1.service3 = s.service1)
OR ( s1.service1 = s.service3
AND s1.service2 = s.service1
AND s1.service3 = s.service2)
OR ( s1.service1 = s.service3
AND s1.service2 = s.service2
AND s1.service3 = s.service1) )
ORDER BY (null)) AS rownumber
FROM services s )
WHERE rownumber > 1;附注:我为Oracle写了这篇文章。我从未使用过Teradata,因此它们可能有不同的分区方式。请参阅http://www.bikinfo.com/HTML/TD/TD_vs_Oracle.html#_Toc_Qualify
https://stackoverflow.com/questions/49991894
复制相似问题