1.删除重复数据(去重)
正确方法:
delete from user where id not in
(select t.min_id from
(select min(id) min_id from user group by name,kemu) t
);
错误方法:
delete from user where id not in
(select min(id) min_id from user group by name,kemu)
;
会报以下错误:
[Err] 1093 - You can't specify target table 'user' for update in FROM clause
注意:
不能先select出同一表中的某些值,再delete这个表(在同一语句中)。
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和Oracle不会出现此问题。
2.查询各科成绩大于80的学生
第一种方法:
select name from user group by name having min(chengji) > 80;
第二种方法:
SELECT
DISTINCT name
from
user
where
name
not in(select DISTINCT name from user where chengji<80);