如何从表中查找最频繁的代码(varchar)

Question

我想从一个表中找到CodeID中最常见的代码。

例如，从原始表

ID     CodeID      Name      Code     Code_group
1         1         A        101          0
2         1         A        102          0
3         1         B        102          0
4         2         C        201          0
5         2         C        201          0
6         2         D        202          0
7         2         E        202          0
8         3         F        101          1
9         3         G        103          1
10        3         G        104          1
11        3         G        104          1

我希望输出如下所示:

ID     CodeID      Name      Code     Code_group    Selected_code
1         1         A        101          0             102
2         1         A        102          0             102
3         1         B        102          0             102
4         2         C        201          0             NULL
5         2         C        201          0             NULL
6         2         D        202          0             NULL
7         2         E        202          0             NULL
8         3         F        101          1             104
9         3         G        103          1             104
10        3         H        104          1             104 
11        3         H        104          1             104

尽管CodeID: 1中的第8个ID的代码相同，但它不在同一个Code_group中。

所以对于CodeID: 1, Selected_code将是102。必须在同一个Code_group中计算。

=======================================

我试过如下所示。我不应该在这里使用ID。从为多

with m as
(
select 
CodeID,
Name,
Code,
Code_group,
cnt,
Selected_code = ROW_NUMBER() over (partition by Code_group order by cnt desc)
from( select CodeID, Name, Code,Code_group
,count(*) over (partition by Code,CodeID) as cnt from tableA
group by CodeID, Name, Code, Code_group,
) as t  
group by  CodeID,
Name,
Code,
Code_group, cnt
)
select a.CodeID,
a.Name,
a.Code,
a.Code_group, b.Code as Selected_code, cnt 
from(select
CodeID,
Name,
Code,
Code_group,Selected_code,
cnt
from m) as a left outer join
(select CodeID,
Name,
Code,
Code_group,Selected_code,
cnt
from m where selected_Code=1) as b on a.CodeID = b.CodeID and a.Code_Group = b.Code_Group 
order by a.CodeID, a.Code_Group

问题是与语句使我的表不同。如果有完全相同的数据，例如ID 1,2，则只显示一行。此外，如果有完全相同的频率，我不能使为NULL。

---

我应该添加什么来获得我想要的输出?或者有更好的方法吗?

Answer 1

CTEcte利用dense_rank()找到Code_group和CodeID的最高频率码

CTEselected检查相同频率的Code并排除它们。

最后的查询只从原始表和LEFT JOIN中选择selected

with 
cte as
(
select Code_group, CodeID, Code
from
(
select Code_group, CodeID, Code, 
r = dense_rank() over (partition by Code_Group, CodeID
order by count(*) desc)
from   tableA
group by Code_group, CodeID,  Code
) c
where c.r = 1
),
selected as
(
select Code_group, CodeID, Code
from
(
select Code_group, CodeID, Code, 
cnt = count(*) over (partition by Code_group, CodeID)
from   cte
) s
where s.cnt = 1
)
select a.*, 
Selected_Code = s.Code
from   tableA a
left join selected s on  a.Code_Group = s.Code_Group
and a.CodeID     = s.CodeID;

,db&lt的在小提琴演示