总结1:
delete user_info as a from user_info as a,
(
select *, min(ID) from user_info group by Name,Password having count(1) > 1
) as b
where a.Name = b.Name and a.Password = b.Password and a.id > b.id;
解释一下:
(1) Mysql中删除语句可以有如下几种写法
delete from user_info
delete user_info from user_info
这两种写法都会清空数据库表.
如果需要为表加别名as ,则必须用第二种,完整的也就是delete user_info as a from user_info as a
(2) b要作为一个临时表,必须包含真正的内容,所以必须有select *;另,如果ID是自动递增的,此时加不加min(ID)都可以,由于group by的存在,系统会自动获取每分组的第一条记录,也就是ID最小的记录,但是如果ID不是自动递增的,此时就得加min(ID)了,否则系统获取的是每分组中记录排在最前的,但排在最前的不一定是ID号最小的,因此加min(ID)还是很有必要的。
(3) group by Name,Password,目的就是要删除Name/Password相同的, 所以由这两项决定批次(似懂非懂)
(4) having. 通俗的讲,它就跟where一样,后面跟的是条件.与where不同的是,它这个条件是其前面的select语句先算出来然后它再用的,此处是group by分出的每一组的数量,即count(*)的值从来都是依赖于group by,而不是前面的*,更不受min(ID)的影响。
(5) count(1),就是对第一个字段count,这样比count(*)省资源啊
(6) count(1) > 1 ,即至少有2条记录
总结2:
如下:删除表中pcName,siteAdress,dateTime,theTitle重复的。
delete from T_NSM_BrowseSiteAudit
where ((pcName, siteAdress, dateTime, theTitle) in (select pcName, siteAdress, dateTime, theTitle from (select pcName, siteAdress, dateTime, theTitle from T_NSM_BrowseSiteAudit group by pcName, siteAdress, dateTime, theTitle having count(*) > 1) B) ) and Id not in ( select min(Id) from (select min(Id) from T_NSM_BrowseSiteAudit GROUP BY pcName, siteAdress, dateTime, theTitle having count(*) > 1) C);
注:
(1)表别名,可认为是临时创建的表,用来存搜出的数据,所以,后面的条件里都要加别名,而delete from后面没必要加别名。
(2)MySQL不支持 ** in (select ** from table)这种方式,必须是** in (select ** from (select ** from table) T)这种啰嗦的方式