mysql删除重复数据

JensLee 2018-05-04 04:12:16
有数据库表history如下



merchantId是商家ID,commodityId是商品ID,price是价格,priceTime是商家修改价格的最后时间,

里面存在大量重复数据,我现在要删除重复数据

重复数据:在同一天里面,同一商家的同一商品含有相同价格。

因为这个表是从两个表中汇总而来,所以会有重复。

我现在写了查询的sql语句有问题:

delete from history h where (h.merchantId,h.commodityId,h.price,h.priceTime) not in (SELECT DISTINCT merchantId,commodityId,price, DATE_FORMAT(priceTime,'%Y-%m-%d') FROM supnuevo_buyer_price_history)



报错:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'h where (h.merchantId,h.commodityId,h.price,h.priceTime) not in (SELECT DISTINCT' at line 1
...全文
1325 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
等待戈多12 2018-05-09
  • 打赏
  • 举报
回复

-- 删除重复,保留priceID最小的一条记录
delete from history
where priceid in(
									select t.priceid from (
											select a.priceid from history a
											where exists(select * from history where merchantId = a.merchantId and commodityId = a.commodityId and price = a.price and DATE_FORMAT(priceTime,'%Y-%m-%d') = DATE_FORMAT(a.priceTime,'%Y-%m-%d') and priceid < a.priceid)
										) t
)

中国风 2018-05-09
  • 打赏
  • 举报
回复
别名去掉 e.g.
delete from history  where (merchantId,commodityId,price,priceTime) not in (SELECT DISTINCT merchantId,commodityId,price, DATE_FORMAT(priceTime,'%Y-%m-%d') FROM supnuevo_buyer_price_history)
JensLee 2018-05-05
  • 打赏
  • 举报
回复
引用 1 楼 sinat_28984567 的回复:
试试这样
delete from history  where (merchantId,commodityId,price,priceTime) not in (SELECT DISTINCT merchantId,commodityId,price, DATE_FORMAT(priceTime,'%Y-%m-%d') FROM supnuevo_buyer_price_history)
大佬,我的数据库表明写的有问题,是supnuevo_buyer_price_history。 然后我改了改delete from supnuevo_buyer_price_history where (merchantId,commodityId,price,priceTime) not in (SELECT DISTINCT merchantId,commodityId,price, LEFT(priceTime,10) FROM supnuevo_buyer_price_history) 这个报错,Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'h where (merchantId,commodityId,price,priceTime) not in (SELECT DISTINCT merchan' at line 1
二月十六 2018-05-04
  • 打赏
  • 举报
回复
试试这样
delete from history  where (merchantId,commodityId,price,priceTime) not in (SELECT DISTINCT merchantId,commodityId,price, DATE_FORMAT(priceTime,'%Y-%m-%d') FROM supnuevo_buyer_price_history)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧