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

Question

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

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".

I hope this makes sense.

Answer 1

You could do it like this:

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