当我在 sql server 中有多个列时如何使用 DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10066166/
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
how to use DISTINCT when I have multiple column in sql server
提问by EmreAltun
I have the following query:
我有以下查询：
select carBrand, carYear, carModel from cars;
what I want is to get only different car names.
我想要的是只获得不同的汽车名称。
I wrote this, but it is not what i want.
我写了这个，但这不是我想要的。
select DISTINCT carBrand, carYear, carModel from Cars;
how can I solve this problem?
我怎么解决这个问题？
回答by Mark Byers
DISTINCT works on the entire row, not a specific column. If you want to get the unique names, select only that column.
DISTINCT 适用于整行，而不是特定的列。如果要获取唯一名称，请仅选择该列。
SELECT DISTINCT carBrand FROM Cars
回答by Yahia
try
尝试
SELECT carBrand , carYear ,carModel
FROM Cars
GROUP BY carBrand , carYear ,carModel;
回答by CodingYoshi
I am not sure why the accepted answer has been accepted and certainly do not understand why it has been upvoted but the OP has the following in the question:
我不确定为什么接受的答案被接受，当然也不明白为什么它被投票，但 OP 在问题中有以下内容：
I wrote this, but it is not what i want.
我写了这个，但这不是我想要的。
select DISTINCT carBrand, carYear, carModel from Cars;
The accepted answer has suggested to use:
接受的答案建议使用：
SELECT carBrand , carYear ,carModel
FROM Cars
GROUP BY carBrand , carYear ,carModel;
which returns the exact same result as the OP's query. Actually the suggestion in the answer (use group by) is not even encouraged to be used for getting distinct results but should be used for aggregation. See thisanswer for more info.
它返回与 OP 查询完全相同的结果。实际上，甚至不鼓励将答案中的建议（使用 group by）用于获得不同的结果，而应用于聚合。有关更多信息，请参阅此答案。
In addition, SQL Server is smart enough to understand that if there is no aggregation function in the query, then the query is actually asking for distinctso it will use distinctunder the hood.
此外，SQL Server 足够聪明，可以理解如果查询中没有聚合函数，那么查询实际上是在请求，distinct因此它将distinct在后台使用。
Distinct will do a distinct on the entire row as @MarkByers has indicated in his answer.
正如@MarkByers 在他的回答中所指出的那样，Distinct 将在整行上做一个不同的处理。
For those who want to test the above, here is a script that will create a table with 3 columns and then fill it with data. Both (distinctand group by) will return the same resultset.
对于那些想要测试上述内容的人，这里有一个脚本，它将创建一个包含 3 列的表，然后用数据填充它。(distinct和group by) 都将返回相同的结果集。
CREATE TABLE [dbo].[Cars](
[carBrand] [varchar](50) NULL,
[carYear] [int] NULL,
[carModel] [varchar](50) NULL
)
go;
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '3M');
Both queries will return this result:
两个查询都会返回这个结果：
carBrand carYear carModel
BMW 2000 328 i
BMW 2000 3M
Conclusion
结论
DO NOT use group byif you want distinct records. Use distinct. Use group bywhen you use aggregate function such as SUM, COUNTetc.
group by如果您想要不同的记录，请不要使用。使用distinct. 使用group by当您使用聚合函数，例如SUM，COUNT等等。
回答by McGarnagle
It depends what you want. For example if you want 'Toyota Corolla' and 'Toyota Camry', but ignore the year, then you could do this:
这取决于你想要什么。例如，如果您想要“Toyota Corolla”和“Toyota Camry”，但忽略年份，那么您可以这样做：
SELECT DISTINCT carBrand + ' ' + carModel AS carName
FROM Cars;
