这个需求怎么用sql查询

Question

我有一个表，类似这样

id	name	tag
1	zhangsan	1
2	lisi	1
3	zhangsan	2

我想要无重复name的所有行，并且tag最小的
取出来的数据这样：
id name tag
1 zhangsan 1
2 lisi 1

请问这种情况如何查询

Answer 1

...

如果你确实需要 id 这个字段的话, 你必须这么写:

select B.id, B.name, B.tag
from 
  (select min(tag) min_tag, name 
    from table1
    group by name) A,
  table1 B
where A.name = B.name and A.min_tag = B.tag;

---

解释一下吧, 在sql标准里, 如果用了group by和聚集函数, 那么select的字段必须是 group by的字段(例子里的name)或者 聚集函数(例子里的min(tag)). 所以

select id, name from table1 group by name

在某些数据库里是会报错的(好像oracle就是?)

但是很多数据库产品没有严格按标准来, 比如mysql:
http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html

In standard SQL, a query that includes a GROUP BY clause cannot refer
to nonaggregated columns in the select list that are not named in the
GROUP BY clause. ... MySQL extends the use of GROUP BY so that the
select list can refer to nonaggregated columns not named in the GROUP
BY clause.

但在这种情况下, 返回的结果集是不确定的. 所以最好在把sql写到产品去之前, 确切的知道它会做什么.

---

select id, name from table1 group by name

在Oracle 12C里报错: ORA-00979 not a GROUP BY expression
http://www.dba-oracle.com/t_ora_00979_not_a_group_by_expression.htm

Answer 2

SELECT * FROM xx GROUP BY `name`;

Answer 3

mssql的写法：SELETE name,min(tag) FROM xx GROUP BY name

Answer 4

觉得应该多看看sql的基本知识...

Answer 5

SELECT id, name, MIN(tag) FROM dis GROUP BY name;

Answer 6

select id, name, min(tag) from table group by name having count(name) = 1;