实用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)

转载于:https://www.cnblogs.com/haiping/articles/2169995.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值