实用SQL语句集合

一、查找重复记录
1。查找全部重复记录： Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
2。过滤重复记录(只显示一条) ：Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title) 注：此处显示ID最大一条记录二。删除重复记录
3。删除全部重复记录（慎用）： Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
4。保留一条： Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title) 注：此处保留ID最大一条记录文章出处：

 

二、查询无重复记录数
select distinct * from 表名

 

三、查找两图层中BSM相同 其他字段不同的记录
select n.bsm from (
select bsm,count(1) as xx from (
select distinct t.* from (select  BSM,YSDM from (
select * from sde.dltb
union all
select * from sde.dltb_1
))t) m group by bsm ) n where n.xx > 1

 

四、查找两图层中BSM不同的记录
select * from sde.dltb where bsm not in (select bsm from sde.dltb_1)

SQL中的查询
select n.bsm from (
select bsm,count(1) as xx from (
select distinct t.* from (select  BSM,YSDM from (
select * from sde.xzdwgx
union all
select * from sde.xzdwgx_1
))t) m group by bsm ) n where n.xx > 1

---过滤NULL与空值的比较
select n.bsm from (
select bsm,count(1) as xx from (
select distinct t.* from (select  BSM,YSDM from (
select * from sde.xzdwgx
union all
select * from sde.xzdwgx_1
) k )t where XZDWMC is null) m group by bsm ) n where n.xx > 1

---所有字段的比较
select z.bsm from (
select distinct * from (
select * from sde.xzdwgx
union all
select * from sde.xzdwgx_1
) j
)z
group by z.bsm having count(z.bsm)>1

 

五、表间字段匹配更新
UPDATE sde.GTZ SET BZL=(SELECT BZL222 FROM sde.GTZ_1 WHERE GTZH=sde.GTZ.GTZH AND QLRMC=sde.GTZ.QLRMC)