如何在MySQL数据库中查找最常见的单词并求平均第二列

问题描述

因此，我在MySQL数据库中有两列文本，示例如下:

So I have two columns of text in a MySQL database, an example would be as follows:

Name             Score
Henry Hodgens    4
Mary Hodgens     8
Jim Servan       2
Jane Servan      4
Hank Servan      6
Sarah Smith      10
Mary Smith       12
Henry Dobbins    2
Henry Jenkins    4

我需要使用PHP运行查询，该查询可以根据名称"中单个单词的最常见出现次数来显示得分"的平均值.因此，这表明，"Servan"的平均值为4，"Henry"的平均值为3.3，"Hodgens"的平均值为6，"Mary"的平均值为10，这是名称"中单词出现次数最多的顺序.

I need to run a query with PHP that can show the average of "Score", based on the most common occurrences of a single word in "Name". So, it would show that "Servan" averages 4, "Henry" averages 3.3, "Hodgens" averages 6, "Mary" averages 10, in the order of most occurrences of the word in "Name".

我希望这是有道理的.

推荐答案

您可以这样做:

SELECT
    AVG(t.Score) AS ScorceAvg,
    t.name
FROM
    (
        SELECT 
            SUBSTRING(Table1.Name,1,INSTR(Table1.Name, ' ')) AS name,
            Table1.Score
        FROM 
            Table1
        UNION ALL
        SELECT 
            SUBSTRING(Table1.Name,INSTR(Table1.Name, ' ')) AS name,
            Score
        FROM 
            Table1
    ) AS t
GROUP BY
    t.name

这篇关于如何在MySQL数据库中查找最常见的单词并求平均第二列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！