当我在sql server中有多个列时，如何使用DISTINCT

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?

我该如何解决这个问题?

4 个解决方案

#1

try

试一试

SELECT carBrand , carYear ,carModel 
FROM Cars 
GROUP BY carBrand , carYear ,carModel;

#2

DISTINCT works on the entire row, not a specific column. If you want to get the unique names, select only that column.

对整个行执行不同的操作，而不是特定的列。如果您想获得唯一的名称，请只选择该列。

SELECT DISTINCT carBrand FROM Cars

#3

It depends what you want. For example if you want 'Toyota Corolla' and 'Toyota Camry', but ignore the year, then you could do this:

这取决于你想要什么。例如，如果你想要“丰田花冠”和“丰田凯美瑞”，但忽略年份，那么你可以这样做:

SELECT DISTINCT carBrand + ' ' + carModel AS carName
FROM Cars;

#4

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 this answer for more info.

返回与OP的查询完全相同的结果。实际上，答案中的建议(use 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 distinct so it will use distinct under the hood.

此外，SQL Server非常聪明，它能够理解，如果查询中没有聚合函数，那么查询实际上是在请求“不同”，因此它将在后台使用“不同”。

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 (distinct and group by) will return the same resultset.

对于那些想要测试上述内容的人，这里有一个脚本，它将创建一个包含3列的表，然后填充数据。两个(distinct和group by)都将返回相同的resultset。

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 by if you want distinct records. Use distinct. Use group by when you use aggregate function such as SUM, COUNT etc.

如果想要不同的记录，不要使用group by。使用不同的。当你使用聚合函数如SUM, COUNT等时，使用group by。

#1

try

试一试

SELECT carBrand , carYear ,carModel 
FROM Cars 
GROUP BY carBrand , carYear ,carModel;

#2