SQL操作常规语法【以后遇到更加好的,我会继续整理】

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