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.[门牌号]