mysql - 列出 MySQL 中共享组的最常见项目

Question

有一个包含组 ID 和项目的表，如下所示：

group   item
-------------
1         A       
1         B
1         C    
1         D     

2         D     
2         A   
2         B   

3         B 
3         C 
3         D

4         A 
4         D 
4         E 

5         A 
5         D 

如何检索与指定项目一起找到的最常见项目？在上表中， A最常见的项目是D。

标签： mysqlsqlcountinner-joinaggregate-functions

Answer 1

您可以自加入、聚合和过滤：

select item, item2 most_frequent_item, cnt
from (
    select t1.item item, t2.item item2, count(*) cnt, 
        rank() over(partition by t1.item order by count(*) desc) rn
    from mytable t1
    inner join mytable t2 on t1.grp = t2.grp and t1.item <> t2.item
    group by t1.item, t2.item
) t
where rn = 1

这仅在 MySQL 8.0 中有效（其中窗口函数可用），并且允许绑定（如果有）（如果您不想要绑定，请使用row_number()而不是rank()）。

DB Fiddle 上的演示：

项目 | most_frequent_item | cnt
:--- | :----------------- | --:
一个 | D | 4
乙| D | 3
C | 乙| 2
C | D | 2
D | 一个 | 4
E | 一个 | 1
E | D | 1

---

在早期版本中，这有点复杂。一个选项使用having子句：

select t1.item item, t2.item most_frequent_item, count(*) cnt
from mytable t1
inner join mytable t2 on t1.grp = t2.grp and t1.item <> t2.item
group by t1.item, t2.item
having count(*) = (
    select count(*) cnt
    from mytable t11
    inner join mytable t21 on t11.grp = t21.grp and t11.item <> t21.item
    where t11.item = t1.item
    group by t11.item, t21.item
    order by count(*) desc limit 1
)
order by t1.item