SQL 对多列使用 select distinct，但所有列都不能是不同的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13174168/
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
using select distinct with multiple columns but all columns musnt be distinct
提问by user983924
I need all columns in a table but two columns must be distinct. I used this code but It check all columns, but I only need two of them will be distinct. How can I satisfy this?
我需要表中的所有列，但两列必须不同。我使用了这段代码，但它检查了所有列，但我只需要其中两个是不同的。我怎样才能满足这个？
select distinct a.personalId, a.fileId, a.name, a.surname, a.address from my_table a
If I used the following code, I can not get the other columns:
如果我使用以下代码，则无法获取其他列：
select distinct a.personalId, a.fileId from my_table a
回答by Colin 't Hart
Postgresql supports the DISTINCT ON syntax: http://www.postgresql.org/docs/current/interactive/sql-select.html
Postgresql 支持 DISTINCT ON 语法：http: //www.postgresql.org/docs/current/interactive/sql-select.html
select distinct on (a.personalId, a.fileId) a.personalId, a.fileId, a.name, a.surname, a.address
from my_table a
See Oracle equivalent of Postgres' DISTINCT ON?for how to do this in Oracle.
看到与 Postgres 的 DISTINCT ON 等效的 Oracle 了吗？了解如何在 Oracle 中执行此操作。
回答by Ariel
What should happen if the other columns are different? How should the database pick which value to show? The answer is "it can't", and since it can't there is no syntax to do this (it's a logical error).
如果其他列不同会发生什么？数据库应该如何选择要显示的值？答案是“它不能”，因为它不能这样做，所以没有语法来做到这一点（这是一个逻辑错误）。
MySQL does have an extension where it will pick values randomly.
MySQL 确实有一个扩展，它会随机选择值。
select a.personalId, a.fileId, a.name, a.surname, a.address
from my_table a
group by a.personalId, a.fileId
But I don't recommend relying on this. You need to rethink the logic you are using.
但我不建议依赖于此。您需要重新考虑您正在使用的逻辑。
