请问怎样用SQL语句删除完全相同的记录呢

不知道怎样用SQL语句删除完全相同的记录，只知道可以用distinct在查询中去重

img

delete from prodstorage t where not exists
(select 1 from prodstorage
   group by ProdCode,ProdName,Spec,Price,TQTY
    having t.rowid=max(rowid));
commit;

用rowid的方式可以实现，如果是完全一致数据需要group by 所有字段，如果是某个字段或者某几个字段的去重，则group by 相应的字段即可

看你的实际情况，如果你有id可以区分重复数据，可以用下面的sql，如果没有的话，就需要用临时表的方式先把重复去重查出来插入临时表，再把临时表数据再插入到现在的表。

重复数据有不同id方式

DELETE 
FROM
    prodstorage
WHERE
    ProdCode IN ( SELECT * FROM ( SELECT ProdCode FROM prodstorage GROUP BY ProdCode HAVING count( ProdCode) > 1 ) a ) 
    AND id NOT IN ( SELECT * FROM ( SELECT min( id) AS id FROM prodstorage GROUP BY ProdCode HAVING count( ProdCode ) > 1 ) b )

重复数据全相同

create table temp_prodstorage(select DISTINCT ProdCode, ProdName,Spec,Price,TQTY from prodstorage);
truncate prodstorage;
insert into prodstorage( ProdCode, ProdName,Spec,Price,TQTY)SELECT * from temp_name_age;
DROP TABLE temp_prodstorage ;

deleat ＊from 表名 where 列名等于（select 相同列名 from 表名）

delete from people

where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1

你这是什么库呀
可以直接 insert overwrite people select distinct *from people

这个设计表的人很坑啊，没设计主键和唯一约束字段吗？
如果是mysql8.0或者是mssql2008以上版本
;with t as (select *,row_number() over (partition by col1,col2,col3(要判断去重的字段列表) order by col1(随便哪个字段 都行)) as rn
from table )
delete t from t where rn>1
这样就可以将数据分组后删除表重复的数据了，每个分组只保留一行

where后面的查询条件重新查一下给起个别名

insert overwrite 表
SELECT * FROM (
select *,ROW_NUMBER()OVER(PARTITION BY PRODNAME,PRODCODE ORDER BY SPEC) RO  FROM (
SELECT '001' PRODCODE,'沐浴露' PRODNAME,'300ml' SPEC ,'30' PRICE,'80' TQTY
UNION ALL
SELECT '002' PRODCODE,'洗发水' PRODNAME,'500ml' SPEC ,'40' PRICE,'40' TQTY
UNION ALL 
SELECT '001' PRODCODE,'沐浴露' PRODNAME,'300ml' SPEC ,'30' PRICE,'80' TQTY) A ) A 
WHERE RO = 1