我有这样一个mysql表:
id employee_number name
1 1 A
2 2 B
3 3 C
4 3 C
5 4 D
6 5 E
7 5 E
8 6 F
9 7 G
8 8 H
9 9 I
10 ....
11 ....
12 ....
many duplicated
employee_number我想删除1行employee_number 3和5(id 3或4和6或7不重要)。我可以看到具有以下sql的重复行,但不知道下一步该做什么。如果有人能在这件事上帮我,我就当学徒了。非常感谢!
SELECT COUNT( id ) , employee_number
FROM `employees`
GROUP BY employee_number更新:
将表new_table作为SELECT *从old_table创建,其中1组按employee_number;
井。我找到了这份声明,而且效果很好。谢谢你们。
发布于 2012-03-09 03:40:47
DELETE FROM `employees`
WHERE ID
NOT IN
(SELECT MAX(B.ID) FROM
(SELECT employee_number
FROM `employees`
GROUP BY employee_number
HAVING COUNT(*)>1) A,
`employees` B
WHERE A.employee_number=B.employee_number
GROUP BY B.employee_number
);发布于 2012-03-09 03:42:34
U可以使用这样的SQL (在甲骨文中):查询重复行SELECT * FROM employees t1 t1.rowid> (选择min(t2.rowid),从employees t2 where t2.name=t1.name);
从员工中删除t1 where t1.rowid>(从员工中选择min(t2.rowid) );
发布于 2012-03-09 03:47:05
你可以自己加入这些桌子。
说不删除相同的记录。ie (不是Employee.id=vtable.id)
如果没有相同的记录,如果employee_number匹配,则删除。
delete from Employee
USING Employee, Employeeas vtable
WHERE (NOT Employee.id=vtable.id)
AND (Employee.employee_number=vtable.employee_number)https://stackoverflow.com/questions/9628500
复制相似问题