sql查找和删除多字段重复项只保留最小rowid行

最新推荐文章于 2022-03-10 11:12:30 发布

小黄人软件

最新推荐文章于 2022-03-10 11:12:30 发布

阅读量690

点赞数

分类专栏： c语言

本文链接：https://blog.csdn.net/chenhao0568/article/details/100577241

版权

c语言专栏收录该内容

41 篇文章 0 订阅

订阅专栏

--sql查找多字段重复项
SELECT Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No FROM alarm WHERE (Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No) IN 
(
  SELECT Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No FROM alarm GROUP BY 
  Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No HAVING count(*) > 1
);

--sql删除多字段重复项，只保留最小rowid行。
DELETE FROM alarm WHERE (Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No) IN (
 SELECT Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No FROM alarm GROUP BY 
 Device_ID, Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No HAVING count(*) > 1
) 
and rowid NOT IN (
    SELECT min(rowid) FROM alarm GROUP BY Device_ID,             
    Device_Type,Sign_Class,Sign_Name,Sign_State,Sign_No HAVING count(*) > 1
);