1:转载QQ群: 群号是105568127【查找重复项】
--表结构介绍:表名:TBLTest,表中字段:sid(主键),sname,ssex,statusid,其中,为测试sname,ssex等,故意设置重复
--查询所有
select * from TBLTest
--查询表不重复行
select * from TBLTest where sname in(select distinct sname from TBLTest group by sname having count(sname)=1)
--显示所有重复行(多条)
select * from TBLTest where sname in (select sname from TBLTest group by sname having count(sname) > 1)
--显示所有重复行(多条)
select distinct * from TBLTest where sname='王芳'
--重复行(取一条)
select * from TBLTest where sid in(select min(sid) from TBLTest where sname='王芳' group by sname)
--显示所有(重复行只显示一条)
select a.* from TBLTest a where 1 > (select count(*) from TBLTest where sname = a.sname and sid > a.sid ) order by a.sname
--显示所有(重复行只显示一条)
select a.* from TBLTest a where not exists(select 1 from TBLTest where sname = a.sname and sid < a.sid)
--显示所有(重复行只显示一条)
select a.* from TBLTest a where sid = (select max(sid) from TBLTest where sname = a.sname) order by a.sname
2:删除重复项并且只保留重复项一条数据
delete a
from (select row=row_number()over(partition by 字段名称 order by 字段名称),* from 表名称)a
where row>1
3:相同表结构数据插入
--如果要将 表1的数据并入表2用以下语句即可
insert into 表2(字段1,字段2) select 字段1,字段2 from b1