MySQL：Group按多列不能给出确切的结果

I have a table that contains 5 columns namely:

我有一个包含5列的表,即:

before_1, before_2, before_3, rule, name

before_1,before_2,before_3,rule,name

where before_1,before_2, and before_3 are the three words before the name/word in a document.

其中before_1,before_2和before_3是文档中名称/单词之前的三个单词。

What I wanted to find was:

我想找到的是:

Which are the two words that occur together before a name. I don't want just the top words, but all the words sorted by the number of occurrences.

在名称之前一起出现的两个单词。我不想只是顶部单词,而是所有单词按出现次数排序。

I tried the following few queries but that didn't work for me.

我尝试了以下几个查询,但这对我不起作用。

select count(before_2),count(before_3),name from data_with_before_words group by name;

I got the same count for both columns, which is not what I was expecting.

我对两个列都有相同的计数,这不是我所期待的。

Example data:

First 5 rows:

前5行:

before_1,before_2,before_2,rule,name

a,league,of,Persona,Amell
the,assasin,of,Persona,Amell
the,league,of,Persona,Amell
a,assasin,of,Persona,Amell
a,league,of,Persona,Amell

Expected Output:

league,of,3,Amell
assasin,of,2,Amell

Any help would be appreciated.

任何帮助,将不胜感激。

3 个解决方案

#1

To get the expected output you can use following query

要获得预期的输出,您可以使用以下查询

select before_2,before_3,name,count(*)
from data_with_before_words 
group by before_2,before_3,name
order by count(*) desc

Demo

#2

Try this Query:

试试这个查询:

select count(res1.comWords) as occurrences, res1.name from (select concat(before_1,"-", before_2) as comWords, name from data_with_before_words) res1 group by res1.name order by occurrences desc;

#3

Try this out

试试吧

SELECT before_2,before_3,name FROM data_with_before_words GROUP BY before_2,before_3,name Having count(*)>=1

#1