已知表info有如下数据:| name | id |
+--------+-------------+
| tom | 1 |
| tom | 1-5-1-3 |
| tom | 1-5-1-3-4 |
| tom | 1-1-1-1 |
| tom | 1-5-1-17 |
| tom | 1-1-2 |
| tom | 1-1-2-10 |
| tom | 1-3-2-5-10 |
| tom | 1-3-3 |
| tom | 1-3-3-1 |
| tom | 1-4-3-1 |
| tom | 1-4-3-1-5 |
| tom | 1-1-1 |
| tom | 1-3-6-4 |
| tom | 1-3-6-4-1-7 |
| tom | 1-4-2-1 |
| tom | 1-4-2-1-1 |请问如何删除,可以得到以下结果
| name | id |
+--------+-------------+
| tom | 1-5-1-3-4 |
| tom | 1-1-1-1 |
| tom | 1-5-1-17 |
| tom | 1-1-2-10 |
| tom | 1-3-2-5-10 |
| tom | 1-3-3-1 |
| tom | 1-4-3-1-5 |
| tom | 1-3-6-4-1-7 |
| tom | 1-4-2-1-1 |
说明:
| name | id |
+--------+-------------+
| tom | 1 |
| tom | 1-5-1-3 |
| tom | 1-5-1-3-4 |因为id=1-5-1-3-4所指的值包含了id=1-5-1-3以及id=1的值,所以只想保留最长id字段值
也就是要删除前两行记录
| tom | 1 |
| tom | 1-5-1-3 |
得出
| name | id |
+--------+-------------+
| tom | 1-5-1-3-4 |不晓得有没有把意思解释清楚哦,呵呵
+--------+-------------+
| tom | 1 |
| tom | 1-5-1-3 |
| tom | 1-5-1-3-4 |
| tom | 1-1-1-1 |
| tom | 1-5-1-17 |
| tom | 1-1-2 |
| tom | 1-1-2-10 |
| tom | 1-3-2-5-10 |
| tom | 1-3-3 |
| tom | 1-3-3-1 |
| tom | 1-4-3-1 |
| tom | 1-4-3-1-5 |
| tom | 1-1-1 |
| tom | 1-3-6-4 |
| tom | 1-3-6-4-1-7 |
| tom | 1-4-2-1 |
| tom | 1-4-2-1-1 |请问如何删除,可以得到以下结果
| name | id |
+--------+-------------+
| tom | 1-5-1-3-4 |
| tom | 1-1-1-1 |
| tom | 1-5-1-17 |
| tom | 1-1-2-10 |
| tom | 1-3-2-5-10 |
| tom | 1-3-3-1 |
| tom | 1-4-3-1-5 |
| tom | 1-3-6-4-1-7 |
| tom | 1-4-2-1-1 |
说明:
| name | id |
+--------+-------------+
| tom | 1 |
| tom | 1-5-1-3 |
| tom | 1-5-1-3-4 |因为id=1-5-1-3-4所指的值包含了id=1-5-1-3以及id=1的值,所以只想保留最长id字段值
也就是要删除前两行记录
| tom | 1 |
| tom | 1-5-1-3 |
得出
| name | id |
+--------+-------------+
| tom | 1-5-1-3-4 |不晓得有没有把意思解释清楚哦,呵呵
SELECT * FROM QT3 C LEFT JOIN
(SELECT B.* FROM QT3 A LEFT JOIN QT3 B ON A.NAME=B.NAME AND INSTR(A.ID,B.ID)>0 AND LENGTH(A.ID)>LENGTH(B.ID))
D ON D.NAME=C.NAME AND D.ID=C.ID WHERE D.ID IS NULL;将SELECT修改为DELETE
select id from info A
where exists(select 1 from info B where instr(B.id,A.id)>0 and length(B.id)<>length(A.id))
)
select b.Id
from info a,info b
where INSTR(a.ID,b.ID)
and a.name = b.name
and LENGTH(A.ID)>LENGTH(B.ID)
先试试这条select语句,合适了,就改成delete就好了
delete from info a,info b
where INSTR(a.ID,b.ID)
and a.name = b.name
那你不能用SQL语句来实现了,还是用存储过程或者程序一条条删除吧。
mysql> delete from info a,info b where INSTR(a.id,b.id) and a.name = b.name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where INSTR(a.id,b.id) and a.name = b.name' at line 1
mysql>
where INSTR(a.ID,b.ID)>1
and a.name = b.name
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a,info b where INSTR(a.id,b.id)>1 and a.name=b.name' at line 1
select 语句是可以行的通的,但是一改成delete就报错了,呵呵!
delete info from info a,info b
where INSTR(a.ID,b.ID)>1
and a.name = b.name
ERROR 1109 (42S02): Unknown table ‘info’in MULTI DELETE