MySql根据字段分组，去除重复数据只留一条

最新推荐文章于 2024-09-22 10:24:49 发布

⁡⁡⁡⁡⁡不三

最新推荐文章于 2024-09-22 10:24:49 发布

阅读量1k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_45433217/article/details/125620646

版权

MySql根据字段分组，去除重复数据只留一条

# 查询重复的数据
Select * From t_801_lift_day Group By FactoryId,Date Having Count(*)>1



# 查出全部的重复数据
SELECT
	* 
FROM
	t_801_lift_day 
WHERE
	FactoryId IN ( SELECT FactoryId FROM t_801_lift_day GROUP BY FactoryId, Date HAVING Count(*)> 1 ) 
	AND Date IN (
	SELECT
		Date 
	FROM
		t_801_lift_day 
	GROUP BY
		FactoryId,
		Date 
HAVING
	Count(*)> 1) ORDER BY FactoryId,Date
	
	# 查询除第一条外的多余数据
	SELECT * FROM t_801_lift_day WHERE Id NOT IN ( SELECT dt.minId FROM ( SELECT MIN(Id) AS minId FROM t_801_lift_day GROUP BY FactoryId,Date ) dt ) ORDER BY FactoryId,Date
	
	
	# 删除除第一条外的多余数据
	DELETE FROM t_801_lift_day WHERE Id NOT IN ( SELECT dt.minId FROM ( SELECT MIN(Id) AS minId FROM t_801_lift_day GROUP BY FactoryId,Date ) dt ) ORDER BY FactoryId,Date