sqlserver删除重复数据只保留一条

 

sqlserver删除重复数据只保留一条

 案例：

id    姓名name 课程kecheng 分数fenshu
1    张三           数学                 69
2    李四           数学                 89
3    张三           数学                 69

--sql server删除重复数据只保留一条
--核心代码 DELETE FROM tablename where id not in (select bid from (select min(id) as bid from tablename group by name,kecheng,fenshu) as b ) ;
--假设表结构：id 姓名name 课程kecheng 分数fenshu
select bid from (select min(id) as bid from tablename group by name,kecheng,fenshu) as b

解释：
select bid from (select min(id) as bid from tablename group by name,kecheng,fenshu) as b
这个子查询的目的是从b中列出讲筛选结果，即bid的集合。
(select min(id) as bid from tablename group by name,kecheng,fenshu) as b
将分组结果中的最小的bid当做一个心的集合当做一个心的子表b，
注意mid(id)一定要有一个别名，这里取的是bid，作为b的一个列名，因为在上一级查询中要用到这个列名。

 

目前网上流行的一种解法是：
1）创建一个临时表，讲要查询的列的存入临时表中
create table temp as select ...
2)在temp表和原始表中进行操作
delete from tablename
3)drop temp...
但是这种做法，不仅浪费空间资源，同时也缺乏友好性。通过观察我们发现这类查询要解决的是如何将子查询中的表与主查询中的表区分开来，因此我们可以考虑用别名的方法，将子查询的结果放到一个别名中。

 

实际操作演示：

-- 重复项 （含原值和重复值）
Select * From [GM_HR_EmployeeDept] WHERE [员工编号] IN( select[员工编号] from [GM_HR_EmployeeDept] group by [员工编号] having count(1)>1) AND [员工编号] is NOT NULL;
Select * From [GM_HR_EmployeeDept] WHERE [员工编号] is NULL;

-- 去重查询
select bid,[员工编号],[姓名] from (select min(id) as bid,[员工编号],[姓名] from [GM_HR_EmployeeDept] group by [员工编号],[姓名]) as b 
-- 去重删除
DELETE FROM [GM_HR_EmployeeDept] where id not in (select bid from (select min(id) as bid from [GM_HR_EmployeeDept] group by [员工编号],[姓名]) as b ) AND [员工编号] is NOT NULL;

 

 

 

···