在SQL中，如果你想要查询表中列B和列C的唯一组合，但不想让列A作为区分条件，可以使用两种方法。一种是通过`GROUP BY`结合`MAX()`函数，只显示每个A、B组合的最大值的C；另一种是使用子查询，筛选出A、B组合的所有唯一记录。如果表中还有其他列，后者可能更为必要。
Let's say:
I want to query colA, colB and colC in my table.
I want to see DISTINCT values but I don't want colA to be a criteria for distinction.
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
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?
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
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.
扫一扫
4366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
