一、查找重复记录
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)