mysql 去重 根据id_mysql去重保留id最小的

百度一些去重，都不是想要的答案，就动手自己来了

CREATE TABLE users(

id VARCHAR(11),

NAME VARCHAR(11),

sex VARCHAR(5)

)

INSERT INTO users VALUES ('1','chen','男');

INSERT INTO users VALUES ('3','chen','男');

INSERT INTO users VALUES ('2','chen','男');

INSERT INTO users VALUES ('4','li','女');

INSERT INTO users VALUES ('1','li','女');

INSERT INTO users VALUES ('2','li','女');

INSERT INTO users VALUES ('1','zhou','男');

INSERT INTO users VALUES ('2','zhou','男');

INSERT INTO users VALUES ('3','zhou','男');

demo数据如下：

需求：只查出name字段id最小的(去重不删除)

首先想到的是用distinct,但是distinct只能返回一个字段，就比较尴尬了

SELECT DISTINCT(a.name)FROM (SELECT * FROM users ORDER BY id )a；

如果：

SELECT a.id,DISTINCT(a.name)FROM (SELECT * FROM users ORDER BY id )a；

则会报错：

错误代码： 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(a.name)from (SELECT * FROM users ORDER BY id )a

LIMIT 0, 1000' at line 1

正解：SELECT * FROM (SELECT * FROM users ORDER BY id )a GROUP BY a.name;