SQL 替代 DISTINCT 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3418731/
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
Alternative to DISTINCT Function
提问by RHPT
Is there a better way to get all distinct values from three columns in one table other than using the DISTINCT function? I've also tried GROUP BY, but there doesn't seem to be any noticeable difference in the cost.
除了使用 DISTINCT 函数之外，还有没有更好的方法可以从一个表中的三列中获取所有不同的值？我也试过GROUP BY，但似乎没有任何明显的成本差异。
SELECT DISTINCT Table1.Col1, Table2.Col1, Table1.Col3
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID
WHERE Table1.Foo = 1865 AND Table2.Type = 1
回答by OMG Ponies
GROUP BYis intended for aggregate function use; DISTINCTjust removes duplicates (based on all column values matching on a per row basis) from visibility.
GROUP BY用于聚合函数；DISTINCT只是从可见性中删除重复项（基于每行匹配的所有列值）。
If TABLE2 allows duplicate values associated to TABLE1 records, you have to use either option. Depends on the data and what you want to see, but you could use a group by & aggregate function to get the highest (using MAX) or lowest (using MIN) values from TABLE2...
如果 TABLE2 允许与 TABLE1 记录关联的重复值，则必须使用任一选项。取决于数据和您想要查看的内容，但您可以使用 group by & 聚合函数从 TABLE2 中获取最高（使用 MAX）或最低（使用 MIN）值...
回答by Dan Williams
Nope, that's how it's done.
不行，就是这么弄的。
Although, you could try:
虽然，你可以尝试：
SELECT DISTINCT Table1.Col1, Table2.Col2
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID AND Table2.Type = 1
WHERE Table1.Foo = 1865
Speed will depend on your data.
速度将取决于您的数据。
Also see sql group by versus distinct
回答by Mark Canlas
Have you tried creating an index on the fields you're selecting?
您是否尝试过在您选择的字段上创建索引？
The relative costs of DISTINCT and GROUP BY make sense. One way of (and probably the way it's using) of processing the data is to sort the rows by the fields you provide. Then the difference between the two is that DISTINCT skips rows that are equal to the previous row, and GROUP by happens to run a count using the same metric of equality.
DISTINCT 和 GROUP BY 的相对成本是有道理的。处理数据的一种方式（也可能是它使用的方式）是按您提供的字段对行进行排序。然后两者之间的区别在于 DISTINCT 跳过与前一行相等的行，而 GROUP by 恰好使用相同的相等度量运行计数。
回答by user2916283
create table #temp (col1 int, col2 int, col3 int)
create index temp_index on #temp (col 1)
insert into #temp
SELECT Table1.Col1, Table2.Col1, Table1.Col3
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID
WHERE Table1.Foo = 1865 AND Table2.Type = 1
select distinct col1, col2, col3
from #temp
回答by Vidar Nordnes
No, there's not, but if you're struggeling with performance on this, you might want to consider indexes. If you provide more details, maybe we can help with this
不，没有，但是如果您在这方面的性能上苦苦挣扎，您可能需要考虑索引。如果您提供更多详细信息，也许我们可以提供帮助
回答by Mr Shoubs
you could try moving the conditions in your 'where' to your joins, though I expect they'll be parsed the same.
您可以尝试将“where”中的条件移动到您的连接中，但我希望它们会被解析相同。
If your trying to increase performance, add an index to Table1.Foo and Table2.Type
如果您想提高性能，请为 Table1.Foo 和 Table2.Type 添加索引
