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;