百度一些去重,都不是想要的答案,就动手自己来了
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;