mysql删除重复数据

[JensLee]

有数据库表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

[二月十六]

试试这样

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]

引用 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

[中国风]

别名去掉 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)

[等待戈多12]

-- 删除重复，保留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
)