MySQL:使用DISTINCT时忽略选定的列 [英] MySQL: Ignore a selected column when using DISTINCT

问题描述

让我们说:

1. 我想查询表中的colA，colB和colC.

1. I want to query colA, colB and colC in my table.

我想查看DISTINCT值，但我不希望colA成为区分标准.

I want to see DISTINCT values but I don't want colA to be a criteria for distinction.

忽略colA是不可行的.

Omitting colA isn't an option.

构造该查询的最佳方法是什么?

What's the best way to structure that query?

推荐答案

这里有两种情况.假设您有数据

There are two cases here. Let's say you have the data

A  B  C   (columns)
a  b  c1
a  b  c2

采用不同的A，B值仅给出一个结果(a，b)，其中C列为两个值. 所以问题是，您是否要查看C的所有值，还是仅查看A和B列的每个不同值的一个值?

Taking distinct values of A, B gives just one result (a,b), with two values for column C. So the question is do you want to see all values of C or just one value for each distinct value of columns A and B?

如果只想看C的一个值，那么可以写

If you want to see just one value of C, then you can write

SELECT A, B, MAX(C) FROM YourTable
  GROUP BY A, B

另一方面，如果要查看C的所有值，则

On the other hand, if you want to see all values for C then

SELECT DISTINCT A, B, C FROM YourTable WHERE ROW(A,B) IN 
  (SELECT A, B FROM YourTable
     GROUP BY A, B)

为您提供.如果表中还有其他列，则需要最后一种选择.

gives you that. This last alternative is needed if there are other columns in the table.

这篇关于MySQL:使用DISTINCT时忽略选定的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！