Mysq语句去重并保留第一条数据。

字体红色加粗为删除语句。

建立表quchongbf:

通过语句建立各个字段的类型,长度来

CREATE TABLE quchongbf(id VARCHAR(6) PRIMARY KEY NOT NULL,Gz1 INT(5) NOT NULL,Gz2 INT(5) NOT NULL, Gz3 INT(5) NOT NULL);

SELECT * FROM quchongbf;

INSERT INTO quchongbf VALUES(1,2600,1200,800);

INSERT INTO quchongbf VALUES(2,2600,550,1400);

INSERT INTO quchongbf VALUES(3,2000,600,900);

INSERT INTO quchongbf VALUES(4,2600,1060,740);

INSERT INTO quchongbf VALUES(5,2600,400,1000);

INSERT INTO quchongbf VALUES(6,1800,900,800);

INSERT INTO quchongbf VALUES(7,2000,700,800);

INSERT INTO quchongbf VALUES(8,1800,800,1100);

查询出字段Gz1重复的数值并根据id排序(默认正序列)单列出来,这也是删除后的结果。SELECT id,Gz1 FROM quchongbf GROUP BY Gz1 ORDER BY id;

删除重复字段

我的学习方式,一个DELETE,两个SELECT。

SELECT 最小id的重命名 min_id。

SELECT 重命名最小id,查询id,查询id和Gz1并根据Gz1分组。

)后的q为表quchongbf的缩写

DELETE FROM quchongbf WHERE id NOT in(

SELECT q.min_id FROM (

SELECT MIN(id)AS min_id,Gz1 FROM quchongbf GROUP BY Gz1)q

)

参考Mysql删除重复数据,保留一条有效数据。

蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主