Mysql的delete语句
发布网友
发布时间：2022-05-01 00:07
共3个回答
热心网友
时间：2022-04-13 01:10
id int,
no char(3),
name varchar(10),
kno char(4),
kname char(6),
score int
);
INSERT INTO student
SELECT 1, '001', 'zhangsan', '0001', 'shuxue', 69 UNION ALL
SELECT 2, '002', 'lisi ', '0002', 'shuxue', 89 UNION ALL
SELECT 3, '001', 'zhangsan', '0001', 'shuxue', 69 UNION ALL
SELECT 4, '001', 'zhangsan', '0001', 'shuxue', 69;
CREATE TABLE tmp AS
SELECT id FROM student
WHERE
EXISTS (
SELECT *
FROM student sub
WHERE
sub.id < student.id
AND sub.no = student.no
AND sub.name = student.name
AND sub.kno = student.kno
AND sub.kname = student.kname
AND sub.score = student.score
);
DELETE FROM student WHERE id IN (SELECT id FROM tmp);
DROP TABLE tmp;
SELECT * FROM student;
+------+------+----------+------+--------+-------+
| id | no | name | kno | kname | score |
+------+------+----------+------+--------+-------+
| 1 | 001 | zhangsan | 0001 | shuxue | 69 |
| 2 | 002 | lisi | 0002 | shuxue | 89 |
+------+------+----------+------+--------+-------+
2 rows in set (0.00 sec)
热心网友
时间：2022-04-13 02:28
(select max(id) from student group by no,name,kno,kname,score)追问还是报You can't specify target table 'student' for update in FROM clause这个错。
追答delete from student where id=
(select top 1 max(id) from student group by no,name,kno,kname,score)
******************************************那就是要分开写语句喽,参见如下批处理****************
SELECT min(id) as id INTO #t
from student group by no,name,kno,kname,score
DELETE from student where id not in (select id from #t)
DROP TABLE #t
热心网友
时间：2022-04-13 04:03
你都插入进去了 还要删除重复的
这个很不明智吧！系统不会这么做的！
sql好像只有删除 全部相同的 可能没有想要的功能
