SQL 选择带有 GROUP BY 一列的所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39816069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select all columns with GROUP BY one column
提问by YCF_L
I have this table:
我有这张桌子：
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+
Is there a way to group by the key to get this result?
有没有办法按键分组来得到这个结果？
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+
Or this result:
或者这个结果：
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 3 | foo |000111 |
+----+-----+----------+
If I use this query:
如果我使用这个查询：
SELECT * FROM sch.mytable GROUP BY(key);
This is not correct I know that, because I should group by all the columns that I need to show.
我知道这是不正确的，因为我应该按我需要显示的所有列进行分组。
Is there a solution for this problem?
这个问题有解决方案吗？
回答by juergen d
A query that works for all DB engines would be
适用于所有数据库引擎的查询将是
select t1.*
from sch.mytable t1
join
(
SELECT min(id) as id
FROM sch.mytable
GROUP BY key
) t2 on t1.id = t2.id
where min(id)is the function that influences which result you get. If you use max(id)you get the other.
min(id)影响您得到的结果的函数在哪里。如果你使用max(id)你得到另一个。
回答by Clodoaldo Neto
select distinct on (key) *
from t
order by key, name
Notice that the order byclause determines which row will win the ties.
请注意，该order by子句确定哪一行将赢得平局。
