Supose I have a table with several columns. I know how to find duplicates, and then to keep just one record with this expression, adding a combinated index...
我有一个有几列的表。我知道如何找到重复项,然后用这个表达式保留一条记录,添加一个组合索引...
ALTER IGNORE TABLE part3 ADD UNIQUE INDEX (gtu, region, trait, pop, author, risk)
...but I would like to be able to control which will be the deleted row; not just the one with the "first row of rows with duplicates on a unique key", as stated here in the mysql doc. I mean, i exactly want to specify that the non-deleted row must be the one with the minor value in column field called zvalue.
...但我希望能够控制删除的行;不仅仅是具有“在唯一键上具有重复的第一行行”的那个,如mysql doc中所述。我的意思是,我确实想要指定未删除的行必须是名为zvalue的列字段中具有次要值的行。
Any suggestion?? Is this possible?
有什么建议??这可能吗?
Thanks!
1 个解决方案
#1
2
Instead of trying to delete what you don't need, try to think of it as a problem whereby you want to pick the ones you want and ignore the rest; like so:
而不是试图删除你不需要的东西,试着把它想象成一个问题,你想要选择你想要的那个而忽略其余的东西;像这样:
CREATE TABLE `other_table` LIKE `part3`;
INSERT INTO `other_table`
SELECT `part3`.*
FROM (SELECT gtu, region, trait, pop, author, risk, MAX(zvalue) AS max_zval
FROM `part3`
GROUP BY gtu, region, trait, pop, author, risk
) `tmp`
INNER JOIN `part3` USING (gtu, region, trait, pop, author, risk)
WHERE `part3`.zvalue = `tmp`.max_zval;
That should create a table other_table that satisfies the unique constraint in your data; only the rows with the highest zvalue are kept, the others removed (skipped).
这应该创建一个表other_table,它满足数据中的唯一约束;只保留zvalue最高的行,删除其他行(跳过)。
#1
2
Instead of trying to delete what you don't need, try to think of it as a problem whereby you want to pick the ones you want and ignore the rest; like so:
而不是试图删除你不需要的东西,试着把它想象成一个问题,你想要选择你想要的那个而忽略其余的东西;像这样:
CREATE TABLE `other_table` LIKE `part3`;
INSERT INTO `other_table`
SELECT `part3`.*
FROM (SELECT gtu, region, trait, pop, author, risk, MAX(zvalue) AS max_zval
FROM `part3`
GROUP BY gtu, region, trait, pop, author, risk
) `tmp`
INNER JOIN `part3` USING (gtu, region, trait, pop, author, risk)
WHERE `part3`.zvalue = `tmp`.max_zval;
That should create a table other_table that satisfies the unique constraint in your data; only the rows with the highest zvalue are kept, the others removed (skipped).
这应该创建一个表other_table,它满足数据中的唯一约束;只保留zvalue最高的行,删除其他行(跳过)。