SQL 查询重复数据,多字段联合重复

SELECT *
FROM pagelist a
WHERE ((SELECT COUNT(*)
FROM pagelist
WHERE ntitle = a.ntitle) > 1)
ORDER BY ntitle DESC
--查询重复的数据


Delete from  pagelist Where ID Not In (Select Max(ID) From pagelist 
Group By ntitle)

--删除重复的数据,保留一个


 delete from dict  where pid=689 and ID in(
select id from dict s1
where id not in (
select max(id) from dict s2
where 
 s1.dict_value=s2.dict_value 
)
)


 delete from ks_kucunribao where ID in(
select id from ks_kucunribao s1
where id not in (
select max(id) from ks_kucunribao s2
where 
 s1.gangkouID=s2.gangkouID 
and s1.intime=s2.intime
and s1.orderindex=s2.orderindex 
and s1.船名=s2.船名 
and  s1.日期= s2.日期 
and  s1.委托人= s2.委托人 
and  s1.收货人= s2.收货人 
and  s1.货种= s2.货种
and  s1.垛位= s2.垛位
and  s1.库存= s2.库存
and  s1.卸船开始日期= s2.卸船开始日期
and  s1.卸船完成日期= s2.卸船完成日期
and  s1.卸船数= s2.卸船数
and  s1.前港结存= s2.前港结存
and  s1.全船结存= s2.全船结存
and  s1.货主= s2.货主
and  s1.入库时间= s2.入库时间
and  s1.货代= s2.货代
and  s1.昨日结存= s2.昨日结存
and  s1.当日调进= s2.当日调进
and  s1.当日调出= s2.当日调出
and  s1.差异= s2.差异
and  s1.库厂= s2.库厂
and  s1.位置= s2.位置
and  s1.来源国= s2.来源国
and  s1.船代= s2.船代
and  s1.提单数= s2.提单数
and  s1.水尺数= s2.水尺数
and  s1.疏港= s2.疏港
and  s1.今日结存= s2.今日结存
and  s1.备注= s2.备注
and  s1.货种类型= s2.货种类型
and  s1.dictID= s2.dictID
 
)
)


SELECT m.* FROM [数据$] m,(
SELECT [小区名称], [楼号],[单元号], [门牌号]
FROM [数据$] 
GROUP BY [小区名称], [楼号],[单元号], [门牌号]
HAVING COUNT(1)>1

) AS m1
WHERE m.[小区名称]=m1.[小区名称] AND m.[楼号]=m1.[楼号] AND m.[单元号]=m1.[单元号]  AND m.[门牌号]=m1.[门牌号]


 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值