mysql-按照某个字段分组，删除重复的记录，报错You can‘t specify target table ‘xx‘ for update in FROM clause

原脚本是（只要有重复的，保留最大BSM的那个，其他的删除）：

DELETE FROM TEMP_ndmx_DELETE WHERE BSM not IN(SELECT max(BSM) FROM TEMP_ndmx_DELETE GROUP BY HH HAVING count(1)>1) ;

因为 mysql不支持这种删除 查询同一张表的操作，解决办法就是 把要删除的数据查询出来做为一个第三方表，然后筛选删除：

保留最大BSM的那个，其他的删除：

delete from TEMP_ndmx_DELETE where hh in(
select hh from (SELECT hh FROM TEMP_ndmx_DELETE GROUP BY HH HAVING count(1)>1) t1 )
and BSM not IN(
select bsm from (SELECT max(BSM) bsm FROM TEMP_ndmx_DELETE GROUP BY HH HAVING count(1)>1) t2 ) ;

---------------------------------------------------------------------------------------------------

如果分组不止HH一个字段，而是HH, DD 多个字段，则改为：

delete from TEMP_ndmx_DELETE where (hh,dd) in(
select hh,dd from (SELECT hh,dd FROM TEMP_ndmx_DELETE GROUP BY HH,dd HAVING count(1)>1) t1 )
and BSM not IN(
select bsm from (SELECT max(BSM) bsm FROM TEMP_ndmx_DELETE GROUP BY HH,dd HAVING count(1)>1) t2 ) ;

----------------------------------------------------------------------