SQL删除重复数据

来源：互联网发布：自助牛排知乎编辑：程序博客网时间：2024/06/05 05:36

<span style="font-size:14px;"> /// <summary>        /// DataTale整张表数据插入数据        /// </summary>        /// <param name="dt">要插入的table数据</param>        /// <param name="tableName">目标数据表名</param>        /// <param name="fieldName">必须提供所有的字段</param>        /// <returns>返回成功，或者失败 true  or false</returns>        public static bool SqlBulkInsertforLeads(DataTable dt, string tableName, string[] fieldName)        {            using (SqlConnection conn = new SqlConnection(Conn.ConnString))            {                conn.Open();                using (SqlBulkCopy bulk = new SqlBulkCopy(Conn.ConnString))                {                    try                    {                        //when the table data handle done                        bulk.DestinationTableName = tableName;                        for (int i = 0; i < fieldName.Length; i++)                        {                            bulk.ColumnMappings.Add(fieldName[i], fieldName[i]);                        }                        bulk.WriteToServer(dt);                        //clear the table repeat data                        using (SqlCommand cmd = conn.CreateCommand())                        {                            StringBuilder temp = new StringBuilder();                            temp.AppendFormat(" DELETE FROM {0} where CategoryID={1} AND ID IN (( ", tableName, Convert.ToInt32(dt.Rows[0][1]));                            temp.AppendFormat(" SELECT ID FROM (select MAX(ID) AS ID,Word,CategoryID from {0} group by Word,CategoryID having COUNT(Word)>1) AS A))", tableName);                            cmd.CommandText = temp.ToString();                            cmd.ExecuteNonQuery();                        }                        return true;                    }                    catch                    {                        return false;                    }                    finally                    {                        conn.Close();                        bulk.Close();                    }                }            }        }</span>

第一步

首先查询出来重复数据的最大ID

select MAX(ID) AS ID,Word,CategoryID from tableName group by Word,CategoryID having COUNT(Word)>1

第二步

把最大的ID查询出来

SELECT ID FROM (select MAX(ID) AS ID,Word,CategoryID from {0} group by Word,CategoryID having COUNT(Word)>1) AS A)

第三步

删除对应ID的值

0 0