(18)mysql删除重复行

概要：查找重复行、WHERE 和 HAVING区别、删除重复行

一、查找重复行

1、select day, count(*) from test GROUP BY day

2、select day, count(*) from test group by day HAVING count(*) > 1

如果完全相同select distinct id(某一列) from table(表名) where (条件) 也可以

二、WHERE 和 HAVING区别

WHERE：在GROUP BY分组和聚合函数  前过滤

HAVING：后过滤

1. where和having都可用

性能where更好，分组前过滤

select dept_id, count(*) from employee group by dept_id having dept_id=1

select dept_id, count(*) from employee where dept_id=1 group by dept_id

2. 前没筛选，只可用where，不可用having

select goods_name,goods_number from sw_goods where goods_price > 100

select goods_name,goods_number from sw_goods having goods_price > 100 //报错！

3. 表里没有字段，只可用having，不可用where

select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000

select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category //报错

三、删除重复行

重复行只保留一行，存临时表，删除全部

临时表导入，创建索引，防止再有重复行写入

https://blog.csdn.net/yexudengzhidao/article/details/54924471

https://blog.csdn.net/zhengzhb/article/details/8590390

https://zhuanlan.zhihu.com/p/169737345