选择无最大金额或分组行的行

发布于 2025-01-27 14:08:33 字数 631 浏览 2 评论 0原文

我有一个名为mytable的表，该表具有A，B列，然后有多个其他列，而值无关紧要。

我想做的就是滤除所有行，当我们按A分组时，给定B的最大行量可能会更容易用示例解释，如果数据看起来像这样，则

A B ...
a f ...
a f ...
a f ...
a g ...
a g ...
b h ...
b h ...
b i ...
b i ...
c j ...
c j ...

输出将被

A B ...
a g ...
a g ...
b i ...
b i ...

过滤。与（a，f）的所有数据相比，与（a，g）中的2个相比，所有数据都有3个。

被过滤（b，h）是因为其中有2个与（b，i）的2个相比，在这种情况下，我们没有任何区别，只要它是其中之一。

被过滤（C，J）是唯一的分组，因此仍然是最大量。

关于如何实施

SELECT A, B, count()
FROM MyTable
GROUP BY A, B

一点

A B count
a f 3
a g 2
b h 2
b i 2
c j 2

这 A然后从原始表中选择它？

原文

I have a table called MyTable which has columns A, B and then multiple other columns where the values don't matter.

What I want to do is filter out all the rows, that when we group the data by A gives the maximum amount of rows for the given B. Probably easier to explain with an example, if the data looked like this

A B ...
a f ...
a f ...
a f ...
a g ...
a g ...
b h ...
b h ...
b i ...
b i ...
c j ...
c j ...

The output would be

A B ...
a g ...
a g ...
b i ...
b i ...

Filtered out the all the data with (a, f) because there is 3 of them compared to only 2 of (a, g).

Filtered out (b, h) because there is 2 of them compared to 2 of (b, i), in this case it makes no difference which we filter out as long it's one of them.

Filtered out(c, j) as it is the only grouping and therefore still the maximum amount.

In term of how to implement this I'm thinking we need to do something like this at some point to get the amount for each grouping:

SELECT A, B, count()
FROM MyTable
GROUP BY A, B

This should initially give something of the form:

A B count
a f 3
a g 2
b h 2
b i 2
c j 2

Not sure at this point how to get the maximum for each A then apply it when selecting from the original table?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拔了角的鹿 2025-02-03 14:08:33

如果您的rdbms使用窗口函数，请在CTE中使用row_number

with cte as(
select
  grouper, 
  value,
  count(value) "number",
  row_number() over (partition by grouper order by count(value) desc) rn
from t
group by 
  grouper,
  value)
select * from cte where rn = 1;

 grouper |值|数字| RN
：------- | ：---- | -----：| ：：
A | f | 3 | 1
b | H | 2 | 1
C | J | 2 | 1

db＆lt;＆gt;＆gt;

If your RDBMS uses window functions use row_number in a CTE

with cte as(
select
  grouper, 
  value,
  count(value) "number",
  row_number() over (partition by grouper order by count(value) desc) rn
from t
group by 
  grouper,
  value)
select * from cte where rn = 1;

grouper | value | number | rn
:------ | :---- | -----: | -:
a       | f     |      3 |  1
b       | h     |      2 |  1
c       | j     |      2 |  1

db<>fiddle here

回复收藏 0 原文

望她远 2025-02-03 14:08:33

这是一个选项：

with tabl1 as(
select col1,col2,count(*)over(partition by col1,col2) cnt
from test1 t1)
select col1,max(col2) from tabl1 t1
WHERE exists(
       select *
       from tabl1 t2
       where t1.cnt
       <=t2.cnt and t1.col1=t2.col1 and t1.col2!=t2.col2
       )
       group by col1

示例：

create table test1 (col1 varchar(1),col2 varchar(1));

insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'g'); 
insert into test1 values ('a', 'g');
insert into test1 values ('b', 'h'); 
insert into test1 values ('b', 'h'); 
insert into test1 values ('b', 'i'); 
insert into test1 values ('b', 'i'); 
insert into test1 values ('c', 'j'); 
insert into test1 values ('c', 'j');

结果：

COL1 | MAX(COL2)
b      i
a      g

Here is one option:

with tabl1 as(
select col1,col2,count(*)over(partition by col1,col2) cnt
from test1 t1)
select col1,max(col2) from tabl1 t1
WHERE exists(
       select *
       from tabl1 t2
       where t1.cnt
       <=t2.cnt and t1.col1=t2.col1 and t1.col2!=t2.col2
       )
       group by col1

Sample:

create table test1 (col1 varchar(1),col2 varchar(1));

insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'f'); 
insert into test1 values ('a', 'g'); 
insert into test1 values ('a', 'g');
insert into test1 values ('b', 'h'); 
insert into test1 values ('b', 'h'); 
insert into test1 values ('b', 'i'); 
insert into test1 values ('b', 'i'); 
insert into test1 values ('c', 'j'); 
insert into test1 values ('c', 'j');

Result: