Given I have columns A B C D ….. Z
鉴于我有A B C D ...... Z
I want to Group-By on A, B , C Having Count(*) > 1 and then for each of those rows, I want to SELECT the rest of the Columns as well that were not included in the aggregate.
我想在A,B,C上有Group-By,Count(*)> 1,然后对于每一行,我想要选择其他列,这些列也没有包含在聚合中。
The result would be like
结果就像
Occurrences A B C D E F G ------- Z
3 e e k q r y e ------- j
3 e e k f t d t ------- y
3 e e k w e q c ------- d
2 f r s w e q c ------- d
2 f r s w e q c ------- d
How can I do that?
我怎样才能做到这一点?
1 个解决方案
#1
1
You don't want GROUP BY, you want ORDER BY. To get the first column, you can use window functions, which are ANSI standard and supported by most databases:
你不想要GROUP BY,你想要ORDER BY。要获取第一列,您可以使用ANSI标准的窗口函数,并且大多数数据库都支持这些函数:
select t.*
from (select count(*) over (partition by a, b, c) as occurrences,
t.*
from t
order by a, b, c
) t
where occurrences > 1;
#1
1
You don't want GROUP BY, you want ORDER BY. To get the first column, you can use window functions, which are ANSI standard and supported by most databases:
你不想要GROUP BY,你想要ORDER BY。要获取第一列,您可以使用ANSI标准的窗口函数,并且大多数数据库都支持这些函数:
select t.*
from (select count(*) over (partition by a, b, c) as occurrences,
t.*
from t
order by a, b, c
) t
where occurrences > 1;